Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

T-SQL string email format issue

I am trying to build an email and have run into an issue. When the stored procedure runs, I get the following error message.

Msg 14624, Level 16, State 1, Procedure sp_send_dbmail, Line 242
At least one of the following parameters must be specified. "@body, @query, @file_attachments, @subject".

My code is below but I am adding each of the requested items. I have narrowed down where the breakdown happens. If I pull out the concatenation "+" everything works as expected. But I have done this before with the concatenation so I am 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
like image 953
joerdie Avatar asked Jun 25 '15 13:06

joerdie


People also ask

How do I validate the format of an email address in SQL?

Using the function like REGEXP_LIKE user can validate the email in MSSQL also. User can write different function in ms sql to validate mail like in oracle. AND PATINDEX('%[^a-z,0-9,@,.,_]%', REPLACE(email, '-', 'a')) = 0; The above function is used to validate the email address in Microsoft sql.

What data type should email be in SQL?

you can use varchar as your data type for email column as emails are usually composed of letters, numbers and special characters.

What is the best type of query for validating the format of an email?

What is the best query for validating the format of an email address in MySQL table? You can use a pure SELECT to validate Email Addresses: SELECT * FROM `users` WHERE `email` NOT REGEXP '^[^@]+@[^@]+\.

How can I check if an email address is valid in SQL?

So, the email validation is done by using pattern matching which can be done by using LIKE operator in MSSQL. It checks for the fixed pattern set by the database designer in the data and displays the data that matches the fixed pattern.


1 Answers

Your code sample is incomplete (you're lacking the declaration of some of the variables used). My hunch is one or more of the variable values (maybe @RespPeriod?) is NULL, and when you do the concatenations for the variable assignments used in your sp_send_dbmail call, you're passing NULL.

Remember, string + NULL = NULL

Right before your call to the sp_send_dbmail, insert these statements...

        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 cause. If it is, chase back the individual parts of whatever variables are resolving as NULL until you find the piece that caused the entire string to be NULL. If it is not, please provide more code so we can look somewhere else.

like image 65
LDMJoe Avatar answered Sep 25 '22 21:09

LDMJoe