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?
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