Menu
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
953
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.
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 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 '^[^@]+@[^@]+\.
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.
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.
65
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
