Stored Proc not working with variables 2

[dnfrantum]

I have the following stored proc. I want to embed the declared variables into the body and subject of outgoing emails, but I continue to get the error "incorrect syntax near '+'" However, when I use literals, the code works.

DECLARE @cardname varchar(100),
        @mailrecipients varchar(100),
        @numatcard varchar(100),
        @trackno varchar(50)

SELECT T1.E_mail,T0.CardName,T0.NumAtCard,T0.U_trackno AS TrackNo, 0 as processed
INTO #maillist
FROM ODLN T0 INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode
WHERE T0.SlpCode = 3 and T0.DocDate > getdate()-1

WHILE (select count(*) from #maillist where processed = 0) > 0

BEGIN
SELECT TOP 1 @cardname = isnull(CardName,''),
             @numatcard = isnull(NumAtCard,''),
             @trackno = isnull(TrackNo,''),
             @mailrecipients = isnull(E_mail,'') 
FROM #maillist
WHERE processed = 0


EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'MyMailProfile',
    @recipients = @mailrecipients,    
    @subject = @numatcard + 'hello', 
    @body = @cardname + ',<br>Thank you for your recent online Goorin purchase.  As the industry leader, we are confident that our quality and craftsmanship will give you many years of enjoyment from your hat.  <br>We are pleased to inform you that order:' + @numatcard + 'has shipped.  <br>You may track your shipment at: [URL unfurl="true"]http://www.goorin.com/'+[/URL] @numatcard + '.htm' ,
    @body_format = 'HTML';


UPDATE #maillist set processed = 1 where CardName = @cardname
END;

SELECT * FROM #maillist;

DROP TABLE #maillist;

Thanks in advance,
Donald

 

[SQLBill]

Try this...(not tested)...

add this to the DECLAREs
DECLARE @newBody VARCHAR(1000)

Then add this before you build the email:

SET @newBody = @cardname + ',<br>Thank you for your recent online Goorin purchase. As the industry leader, we are confident that our quality and craftsmanship will give you many years of enjoyment from your hat. <br>We are pleased to inform you that order:' + @numatcard + 'has shipped. <br>You may track your shipment at:

http://www.goorin.com/'+

@numatcard + '.htm'

Finally, in the email section do this:

@body = @newBody,

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[dnfrantum]

The error code "incorrect syntax near '+'" is still persisting.

Here is the code updated to reflect your recommendation:

DECLARE @cardname varchar(100),
        @mailrecipients varchar(100),
        @numatcard varchar(100),
        @trackno varchar(50),
        @newBody varchar(1000)

SELECT T1.E_mail,T0.CardName,T0.NumAtCard,T0.U_trackno AS TrackNo, 0 as processed
INTO #maillist
FROM ODLN T0 INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode
WHERE T0.SlpCode = 3 and T0.DocDate > getdate()-1

WHILE (select count(*) from #maillist where processed = 0) > 0

BEGIN
SELECT TOP 1 @cardname = isnull(CardName,''),
             @numatcard = isnull(NumAtCard,''),
             @trackno = isnull(TrackNo,''),
             @mailrecipients = isnull(E_mail,'') 
FROM #maillist
WHERE processed = 0

SET @newBody = @cardname + ',<br>Thank you for your recent online Goorin purchase.  As the industry leader, we are confident that our quality and craftsmanship will give you many years of enjoyment from your hat.  <br>We are pleased to inform you that order:' + @numatcard + 'has shipped.  <br>You may track your shipment at: [URL unfurl="true"]http://www.goorin.com/'+[/URL] @numatcard + '.htm'
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'MyMailProfile',
    @recipients = @mailrecipients,    
    @subject = @numatcard + 'hello', 
    @body = @newBody
    @body_format = 'HTML';


UPDATE #maillist set processed = 1 where CardName = @cardname
END;

SELECT * FROM #maillist;

DROP TABLE #maillist;

Thanks in advance,
Donald

 

[gmmastros]

try doing the same thing with the subject.

[tt]@subject = @numatcard + 'hello'[/tt]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TheBugSlayer]

I suggest that you build @newBody one piece at a time and view the content in SSMS, as in executing a SELECT @newBody. Comment out the statement that sends the email and try. I am sure you will find the offendind peace.

Good luck.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[SQLBill]

Good catch George. I missed that he was also 'building' the @subject value.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[gmmastros]

SQLBill,

I stared at this for a while and didn't see anything wrong with it. Of course, once I saw your accurate suggestion, it clicked.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom