Dynamic SQL To Send HTML Email

Question

I am creating a cursor to iterate the values stored in my table, then attempting to use dynamic sql to set the select statement for my html body email. However, anytime I try to execute my statement I get an incorrect syntax error. Is this possible to do? SQL Server 2008

Declare @name varchar(100),@bodytext nvarchar(max), @subject varchar(200)
Create Table #info
(
 name varchar(100)
 ,itemsold varchar(100)
)
Insert Into #info Values
('ZZZZZZ', 'First'),
('CCCCCC', 'Last'),
('EEEEE', 'Green'),
('XXX', 'Blue'),
('QQQ', 'Red')
DECLARE mailcursor CURSOR FOR
SELECT DISTINCT(name) FROM #info 
OPEN mailcursor
FETCH NEXT FROM mailcursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
 set @bodytext = cast( (
 select td = '<font color="#000000" face="verdana" size="2">' + name + '</font></td><td><font color="#000000" face="verdana" size="2">' + itemsold + '</font></td> '
 from (
 'Select * from #info Where name = ''' +@name + ''' '
 ) as d
 for xml path( 'tr' ), type ) as varchar(max) )
 set @bodytext
 = '<table cellpadding="4" cellspacing="0" border="1" bordercolor="#024d6d">'
 + '<tr><th bgcolor=#024d6d><font color="#ffffff" face="verdana" size="2">name</font></th><th bgcolor=#024d6d><font color="#ffffff" face="verdana" size="2">itemsold</font></th></tr>'
 + replace( replace( @bodytext, '&lt;', '<' ), '&gt;', '>' )
 + '<tr><th bgcolor=#024d6d><font color="#ffffff" face="verdana" size="2">name</font></th><th bgcolor=#024d6d><font color="#ffffff" face="verdana" size="2">itemsold</font></th></tr>'
 + '<table>'
 Set @subject = 'Sent Through Code'
 exec msdb.dbo.sp_send_dbmail
 @profile_name = 'DatabaseMail',
 @recipients = '[email protected]',
 @body_format = 'HTML',
 @from_address = '[email protected]',
 @body = @bodytext,
 @subject = @subject;
 FETCH NEXT FROM mailcursor INTO @name 
END
CLOSE mailcursor
DEALLOCATE mailcursor 
--Drop Table #info

And my error message is:

Msg 102, Level 15, State 1, Line 26
Incorrect syntax near 'Select * from #info Where name = ''.

• András Váczi: dba.stackexchange.com/a/113026/6219

Answer 1

Currently you have:

from 
(
 'Select * from #info Where name = ''' +@name + ''' '
) as d

This isn't valid, because you can't say FROM ('some query'), that's like saying FROM ('something that is not a query') because it is just a string. Try:

from
(
 Select * from #info Where name = @name
) as d

...in other words, why all the string wrapping? I did not parse the code, so there may be other problems, too. But that's the immediate one.