Sql-server – Dynamic SQL To Send HTML Email

cursorsdynamic-sqlsql-server-2008

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 = 'foxtrotalphamale12343124@gmail.com',
        @body_format = 'HTML',
        @from_address = 'redfirebluestonesgreeneyes16@gmail.com',
        @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 = ''.

Best Answer

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.

Related Question