T-SQL string output

Question

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

+3

sql-server tsql ssms-2012

joerdie June 25. 15 at 13:33

source to share

1 answer

LDMJoe · Accepted Answer · 2015-06-25T13:44:30+0000

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.

T-SQL string output

More articles: