sql server - T-SQL string email format issue -
i trying build email , have run issue. when stored procedure runs, following error message.
msg 14624, level 16, state 1, procedure sp_send_dbmail, line 242
@ least 1 of following parameters must specified. "@body, @query, @file_attachments, @subject".
my code below adding each of requested items. have narrowed down breakdown happens. if pull out concatenation "+" works expected. have done before concatenation not sure 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 select theid #temptbl open curs fetch next curs @theid while @@fetch_status = 0 begin select * #temptbl tblmaintbl theid = @theid declare @iscomplete bit = 1 if exists (select * #temptbl complete = 0) begin set @iscomplete = 0 end if @iscomplete = 1 begin set @subjectline = 'testing ' + @respperiod + ' testing.' set @contactemail = (select salesemail #temptbl theid = @theid) set @key = (select responsekey #temptbl 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 curs @theid end close curs deallocate curs
your code sample incomplete (you're lacking declaration of of variables used). hunch 1 or more of variable values (maybe @respperiod?) null, , when concatenations variable assignments used in sp_send_dbmail call, you're passing null.
remember, string + null = null
right before call 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 become apparent if cause. if is, chase individual parts of whatever variables resolving null until find piece caused entire string null. if not, please provide more code can somewhere else.
Comments
Post a Comment