T-SQL string output
I am trying to create an email and am running into a problem. When the stored procedure runs, the following error message appears.
Msg 14624, Level 16, State 1, Procedure sp_send_dbmail, Line 242 You
must specify at least one of the following parameters. "@body, @query, @file_attachments, @subject".
My code is below, but I am adding each of the requested items. I narrowed down where the breakdown occurred. If I pulled out the "+" concatenation, everything works as expected. But I've done this before with concatenation, so I'm not sure what is different.
DECLARE @RespPeriod varchar(20)
DECLARE @SubjectLine varchar(100)
DECLARE @ContactEmail varChar(100)
DECLARE @AAEAPVSupplierID int
DECLARE @key varchar(50)
DECLARE @formattedURL varchar(100)
DECLARE @emailBody varchar(max)
DECLARE Curs Cursor
FOR
SELECT theID FROM #temptbl
OPEN Curs
FETCH NEXT FROM Curs INTO @theID
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT *
INTO #temptbl
FROM tblmainTbl
WHERE theID = @theID
DECLARE @isComplete Bit = 1
IF EXISTS (SELECT * FROM #temptbl WHERE Complete = 0)
BEGIN
SET @isComplete = 0
END
IF @isComplete = 1
BEGIN
SET @SubjectLine = 'Testing ' + @RespPeriod + ' Testing.'
SET @ContactEmail = (SELECT SalesEmail FROM #temptbl WHERE theID = @theID)
SET @key = (SELECT ResponseKEY FROM #temptbl WHERE theID = @theID)
SET @formattedURL = 'http://www.something.com/something.aspx?rkey=' + @key
SET @emailBody = '<html>Dear BlaBlaBla' + @RespPeriod + ' ' + @formattedURL + '">' + @formattedURL + '</a></html>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SMTPProfile'
,@recipients = @ContactEmail
,@subject = @SubjectLine
,@body = @emailBody
,@body_format = 'HTML'
END
DROP TABLE #temptbl
FETCH NEXT FROM Curs INTO @theID
END
CLOSE Curs
DEALLOCATE Curs
source to share
Your code sample is incomplete (you are missing the declaration of some of the variables used). My guess is one or more variable values (@RespPeriod perhaps?) Is NULL, and when you do concatenations for the variable assignments used in your call sp_send_dbmail
, you are passing NULL.
Remember, string + NULL = NULL
sp_send_dbmail
Insert these instructions right before calling ...
PRINT '--------------'
PRINT '@SubjectLine = ' + ISNULL(@SubjectLine, 'NULL')
PRINT '@ContactEmail = ' + ISNULL(@ContactEmail, 'NULL')
PRINT '@key = ' + ISNULL(@key, 'NULL')
PRINT '@formattedURL = ' + ISNULL(@formattedURL, 'NULL')
PRINT '@emailBody = ' + ISNULL(@emailBody, 'NULL')
PRINT '--------------'
It should quickly become apparent if this is your reason. If so, chase the individual portions of any variables that resolve to NULL until you find a snippet that results in the entire string being NULL. If not, please provide more code so we can look elsewhere.
source to share