0

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 = ''.

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.