April 11, 2017 at 10:48 am
I have a variable that i am assigning HTML code for an email inside SQL Server. Which normally done like so:
@Body2= 'HTML code'
The problem I'm having is I need to assign HTML code to the variable in a loop, depending on the amount of rows in a Table. So it'd look like this:
while EXISTS (Select top 1 [ID] FROM [Table] where [Sent] is null )
SELECT TOP 1 @ID = [ID]
FROM Table
Where [Sent] is null
@Body2 = @Body2 + 'HTML code'
update table
set [Sent] = 'Mailed'
where [Sent] is null and [ID] = @ID
The line below is the problem: I am trying to add a HTML block of code for each row, after another block as it loops through the rows in the table. But this just blanks out the variable string@Body2 = @Body2 + 'HTML code'
April 11, 2017 at 10:55 am
chriso 73655 - Tuesday, April 11, 2017 10:48 AMI have a variable that i am assigning HTML code for an email inside SQL Server. Which normally done like so:
@Body2= 'HTML code'The problem I'm having is I need to assign HTML code to the variable in a loop, depending on the amount of rows in a Table. So it'd look like this:
while EXISTS (Select top 1 [ID] FROM [Table] where [Sent] is null )SELECT TOP 1 @ID = [ID]
FROM Table
Where [Sent] is null@Body2 = @Body2 + 'HTML code'
update table
set [Sent] = 'Mailed'
where [Sent] is null and [ID] = @IDThe line below is the problem: I am trying to add a HTML block of code for each row, after another block as it loops through the rows in the table. But this just blanks out the variable string
@Body2 = @Body2 + 'HTML code'
Can you post the full code with some sample data and expected results? Read the articles on my signature to know how to do it.
We'll give you an option to improve the code by removing the loop and using less steps.
April 11, 2017 at 11:13 am
Here is the full code:
DECLARE @recipientsX varchar(150)
DECLARE @copy_recipientsX varchar(150)
DECLARE @blind_copy_recipientsX varchar(150)
DECLARE @SubjectLineX varchar(150)
DECLARE @attachmX1 varchar(100)
DECLARE @attachmX2 varchar(100)
DECLARE @header varchar(MAX)
DECLARE @RecordsProcessed nvarchar(10)
DECLARE @CompanyName varchar(100)
DECLARE @WOnum varchar(10)
DECLARE @ScheduledCompletion varchar(50)
DECLARE @Details varchar(100)
DECLARE @JobName varchar(150)
DECLARE @WaitOnClient varchar(10)
DECLARE @InData varchar(20)
DECLARE @OutArtProofs varchar(20)
DECLARE @InMatrix varchar(20)
DECLARE @InArtApproval varchar(20)
DECLARE @InPieces varchar(20)
DECLARE @OutCoupProofs varchar(20)
DECLARE @InCoupContent varchar(20)
DECLARE @InCoupApproval varchar(20)
DECLARE @InPostage varchar(20)
DECLARE @QcRep varchar(20)
DECLARE @PieceCount varchar(20)
DECLARE @counter int
Declare @body2 nvarchar(MAX)
Declare @FullBody varchar(MAX)
Declare @Footer varchar(MAX)
Declare @ID varchar(20)
SET @CompanyName = 'Company'
SET @recipientsX = 'name@email.com'
SET @SubjectLineX = 'Reports'
SET @header = '<html><body><div>
<table border=0 cellpadding=0 cellspacing=0 bordercolor=#111111 width=625 height=100 style=border-collapse: collapse>
<img src=http://website.com/1.png width=625></td>
<p align=left><font size=1></font><font size=1 face=Verdana>'+convert(nvarchar(10),getdate(),101)+'</font></td>
<tr><td width=300 colspan=2 height=1><p align=Center><font face=Verdana><font color=#013C5B><font size="6"><b>'+@CompanyName+'</b></font></td></tr><tr>
<td width=300 colspan=2 height=1></td></tr><tr><td width=300 colspan=2 height=10>'
while EXISTS (Select top 1 [ID] FROM [Table] where [Sent] is null )
BEGIN
SELECT TOP 1 @ID = [ID], @CompanyName = [Company Name], @WOnum = [WO NUM],@JobName = [MAILER NAME], @Details = [JSDETAILS],
@WaitOnClient = [JSWAITONCLIENT], @ScheduledCompletion = [SCHEDULED COMPLETION], @InData = [IN_Data], @OutArtProofs = [OUT_ArtProofs],
@InMatrix = [IN_Matrix], @InArtApproval = [IN_ArtApproval], @InPieces = [IN_Pieces], @OutCoupProofs = [OUT_CoupProofs],
@InCoupContent = [IN_CoupContent], @InCoupApproval = [IN_CoupApproval], @InPostage = [IN_Postage], @QcRep = [QC_Rep], @PieceCount = [PIECE COUNT]
FROM [Table] where [Sent] is null
Set @Body2 = @Body2 + '<p align=center><font face=Verdana size=4 color=#013C5B>'+@JobName+'</font></td></tr>
<td width=300 colspan=2 height=1></td></tr><tr><td width=600 colspan=2 height=10>
<p align=center><font color=red><font face=Verdana size=3>'+@Details+'</font></td></tr>
<TABLE BORDER="0" CELLPADDING="3" CELLSPACING="3" style=margin-left:50px;>
<td width=625 colspan=2 height=3></td>
<tr><th col align=left><font face=Verdana size=2 color=#013C5B>W/O: 39' +@WOnum+ '<th col align=left><font face=Verdana size=2 color=#013C5B>Qty: '+@PieceCount+'</font></tr>
<tr><th col align=left><font face=Verdana size=2 color=#013C5B> Database(s): ' +@InData+ '<th col align=left><font face=Verdana size=2 color=#013C5B>Art Proofs: '+@OutArtProofs+'</font></tr>
<tr><th col align=left><font face=Verdana size=2 color=#013C5B>Data Matrix: ' +@InMatrix+ '<th col align=left><font face=Verdana size=2 color=#013C5B>Proofs Approved: '+@InArtApproval+'</font></tr>
<tr><th col align=left><font face=Verdana size=2 color=#013C5B>Art or Pieces: ' +@InPieces+ '<th col align=left><font face=Verdana size=2 color=#013C5B>Content Proofs: '+@OutCoupProofs+'</font></tr>
<tr><th col align=left><font face=Verdana size=2 color=#013C5B>Text Content: ' +@InCoupContent+ '<th col align=left><font face=Verdana size=2 color=#013C5B>Content Approved: '+@InCoupApproval+'</font></tr>'
update [Table]
Set [Sent] = 'Mailed'
where [Sent] is null AND @ID = [ID]
END
END
Set @Footer = '<tr><td width=300 align=left height=3 valign=bottom colspan=2>
</table>
<p align=left><img src=http://website.com/footer.png width=625></td></tr><tr>
<td width=300 align=left height=2 valign=bottom colspan=2>
</td></tr></table></div></body></html>'
Set @FullBody = @header + @body2 + @Footer
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'MailProfile',
@recipients = @recipientsX,
@subject = @SubjectLineX,
@body = @FullBody,
@body_format = 'HTML' ;
END
print @body2
My expected results would look something like this (if there were only 4 records in my table) Essentially it would assign the HTML in @body2 for each row in the Table:
@Header HTML
@body2 Html block for row 1
@body2 Html block for row 2
@body2 Html block for row 3
@body2 Html block for row 4
@Footer HTML
April 11, 2017 at 11:36 am
Your problem is that you have not initialised your variables.
DECLARE @body2 NVARCHAR(MAX);
SET @body2 = @body2 + N'"Hello World"';
SELECT @body2; -- Returns NULL
Also, if any of your variables that you are building into your html string is null, you will also get a null result. You need to wrap each variable in ISNULL(), or COALESCE()
April 11, 2017 at 11:49 am
What do you mean by "have not initialised your variables"?
Also, there are no NULL values in the data currently.
April 11, 2017 at 12:04 pm
I figured it out! This is what worked:
@Body2 = ISNULL(@Body2,'') + 'HTML code'
April 11, 2017 at 12:08 pm
Here's a version that doesn't use a loop.
DECLARE @recipientsX varchar(150)
,@copy_recipientsX varchar(150)
,@blind_copy_recipientsX varchar(150)
,@SubjectLineX varchar(150)
,@attachmX1 varchar(100)
,@attachmX2 varchar(100)
,@header varchar(MAX)
,@CompanyName varchar(100)
,@body2 nvarchar(MAX)
,@FullBody varchar(MAX)
,@Footer varchar(MAX) ;
SET @CompanyName = 'Company';
SET @recipientsX = 'name@email.com';
SET @SubjectLineX = 'Reports';
SET @header = '<html><body><div>
<table border=0 cellpadding=0 cellspacing=0 bordercolor=#111111 width=625 height=100 style=border-collapse: collapse>
<img src=http://website.com/1.png width=625></td>
<p align=left><font size=1></font><font size=1 face=Verdana>'+convert(nvarchar(10),getdate(),101)+'</font></td>
<tr><td width=300 colspan=2 height=1><p align=Center><font face=Verdana><font color=#013C5B><font size="6"><b>'+@CompanyName+'</b></font></td></tr><tr>
<td width=300 colspan=2 height=1></td></tr><tr><td width=300 colspan=2 height=10>';
SELECT @Body2 += '<p align=center><font face=Verdana size=4 color=#013C5B>' + [MAILER NAME] + '</font></td></tr>
<td width=300 colspan=2 height=1></td></tr><tr><td width=600 colspan=2 height=10>
<p align=center><font color=red><font face=Verdana size=3>' + [JSDETAILS] +'</font></td></tr>
<TABLE BORDER="0" CELLPADDING="3" CELLSPACING="3" style=margin-left:50px;>
<td width=625 colspan=2 height=3></td>
<tr><th col align=left><font face=Verdana size=2 color=#013C5B>W/O: 39' +[WO NUM] + '<th col align=left><font face=Verdana size=2 color=#013C5B>Qty: '+[PIECE COUNT] +'</font></tr>
<tr><th col align=left><font face=Verdana size=2 color=#013C5B> Database(s): ' +[IN_Data] + '<th col align=left><font face=Verdana size=2 color=#013C5B>Art Proofs: '+[OUT_ArtProofs] +'</font></tr>
<tr><th col align=left><font face=Verdana size=2 color=#013C5B>Data Matrix: ' +[IN_Matrix] + '<th col align=left><font face=Verdana size=2 color=#013C5B>Proofs Approved: '+[IN_ArtApproval] +'</font></tr>
<tr><th col align=left><font face=Verdana size=2 color=#013C5B>Art or Pieces: ' +[IN_Pieces] + '<th col align=left><font face=Verdana size=2 color=#013C5B>Content Proofs: '+[OUT_CoupProofs] +'</font></tr>
<tr><th col align=left><font face=Verdana size=2 color=#013C5B>Text Content: ' +[IN_CoupContent] + '<th col align=left><font face=Verdana size=2 color=#013C5B>Content Approved: '+[IN_CoupApproval] +'</font></tr>'
FROM [Table]
WHERE [Sent] is null;
UPDATE [Table]
SET [Sent] = 'Mailed'
WHERE [Sent] is null;
Set @Footer = '<tr><td width=300 align=left height=3 valign=bottom colspan=2>
</table>
<p align=left><img src=http://website.com/footer.png width=625></td></tr><tr>
<td width=300 align=left height=2 valign=bottom colspan=2>
</td></tr></table></div></body></html>';
Set @FullBody = @header + @body2 + @Footer;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'MailProfile',
@recipients = @recipientsX,
@subject = @SubjectLineX,
@body = @FullBody,
@body_format = 'HTML' ;
PRINT @body2;
April 11, 2017 at 12:24 pm
Instead of using a loop - you can create a query that output the table data as an HTML table. here is a simple example:
Set Nocount On;
Declare @body nvarchar(max)
, @xmlResults varchar(max)
, @tableHeader varchar(max);
--==== Create the table header
Set @tableHeader = cast((Select html.hdr1 As th, ''
, html.hdr2 As th, ''
, html.hdr3 As th, ''
, html.hdr4 As th, ''
From (
Values ('Column1', 'Column2', 'Column3', 'Column4')
) As html(hdr1, hdr2, hdr3, hdr4)
For xml Path('tr'), elements) As varchar(max));
--==== Get the results as an XML table
Set @xmlResults = cast((Select t.col1 As td, ''
, t.col2 As td, ''
, t.col3 As td, ''
, t.col4 As td, ''
From dbo.Table t
For xml Path('tr'), elements) As varchar(max));
--==== Setup the CSS style of the message
Set @body = '<style type=''text/css''>';
Set @body += '{font-family:Tahoma, Arial, Helvetica, sans-serif; font-smoothing:always; width:100%; border-collapse:collapse;}';
Set @body += 'td {font-size:10pt; text-align:center; border:1px DarkCyan solid; padding:2px 2px 2px 2px;}';
Set @body += 'th {font-size:10pt; text-align:left; padding:2px 2px 2px 2px; background-color:DarkCyan; color:White;}';
Set @body += 'name tr {color:Black; background-color:DarkCyan;}';
Set @body += '</style>'
--==== Setup the body of the message
Set @body += '<html><body><p>Some Text Here</p> ';
--==== Setup the table...
Set @body += '<table style="margin-left:30px">' + @tableHeader + @xmlResults + '</table>';
--==== Close the body and html
Set @body += '</body></html>';
The issue you are having is the fact that you are trying to add a value to a NULL and that results in a NULL value. This statement 'DECLARE @body2 nvarchar(max)' declares the variable and sets it to a NULL value. Until you set that value to a non-null value - anything you attempt to add to it will become NULL. Change the declaration to DECLARE @body2 nvarchar(max) = ''; and that will solve your immediate problem.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply