REPLACE statement on T-SQL Email body causes null values

Question

I have a simple procedure that sends an email on a user creation. To populate the username and fullname fields, I am loading the body of the email from a config table and then using REPLACE to replace placeholders.

SELECT  @BodyMessage = ConfigValue , @Email = email 
FROM dbo.ConfigValues
WHERE ConfigName = 'ADNotification'

IF @Email = '' SET @Email = @DefaultEmail

SET @BodyMessage = REPLACE(@BodyMessage, 'FullName', @FullName)
SET @BodyMessage = REPLACE(@BodyMessage, 'UserName', @UserName)
Select @BodyMessage

SET @SubjectLine = 'User Account Created'

EXEC msdb.dbo.sp_send_dbmail @profile_name='EUI', 
@recipients=@Email, @subject=@SubjectLine,
@body_format = 'TEXT', @body= @BodyMessage

When this runs, the @BodyMessage is blank. If I comment out the two REPLACE statements, the email sends just fine (like this)

SELECT  @BodyMessage = ConfigValue , @Email = email 
FROM dbo.ConfigValues
WHERE ConfigName = 'ADNotification'

IF @Email = '' SET @Email = @DefaultEmail

--SET @BodyMessage = REPLACE(@BodyMessage, 'FullName', @FullName)
--SET @BodyMessage = REPLACE(@BodyMessage, 'UserName', @UserName)
Select @BodyMessage

SET @SubjectLine = 'User Account Created'

EXEC msdb.dbo.sp_send_dbmail @profile_name='EUI', 
@recipients=@Email, @subject=@SubjectLine,
@body_format = 'TEXT', @body= @BodyMessage

I added the SELECT @Bodymessage statement recently based on some other feedback; the code runs the same with or without the statement. If I check the Sent Mail table in the msdb database, the body is null.

What I am looking to do is have the replace statements correctly replace the fields. What am I doing wrong?

Where are you defining `@FullName` / `@UserName`? If they are NULL, your resulting `@BodyMessage` will be NULL after the replace. — Dave C, Jul 07 '14 at 20:26
Where are the parameters '@FullName', and '@Email' being set? — MDiesel, Jul 07 '14 at 20:27
That was it. WE were constructing @FullName with MiddleName in it, and it isn't required to be set. I didn't know about that behavior of REPLACE. Thanks! — Bill Sempf, Jul 07 '14 at 20:40
You are quite welcome. Glad I was able to help. NULL can cause some ugly issues when you are not familiar with how it works in some situations. — Sean Lange, Jul 07 '14 at 20:41

score 3 · Accepted Answer · answered Jul 07 '14 at 20:30

Almost certainly either @FullName or @UserName IS NULL. This will cause your REPLACE function to return NULL. Check the value of both of those. Alternately you could add an ISNULL inside your replace functions.

Something like this.

SET @BodyMessage = REPLACE(@BodyMessage, 'FullName', ISNULL(@FullName, ''))
SET @BodyMessage = REPLACE(@BodyMessage, 'UserName', ISNULL(@UserName, ''))
Select @BodyMessage

REPLACE statement on T-SQL Email body causes null values

1 Answers1