sql stored procedure conextually problem with assigning value to a variable within a loop

looknow12
looknow12 used Ask the Experts™
on
I am having a problem with this syntax.  It appears SQL does not like @variable = 'Hello world ' +@addsentence

See full code here


WHILE @@FETCH_STATUS = 0
   BEGIN

    --Sending Mail
    EXEC msdb.dbo.sp_send_dbmail @recipients = @recipientemail,
          @subject = 'Your '+@monthlyaccountdescription+' Program at the Wash'
          @body = '<p>Dear Neil,</p>
				   <p>Your credit card ending with an expiration date of DDYY has been charged in the amount of $49.95 for the Program.</p>
				   <p>Thank you for your business.</p>',
          @body_format = 'HTML';
             
      FETCH NEXT FROM Mail_Cursor
      INTO @recipients, @subject, @body;
   END;

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Commented:
First, try adding a comma after this line:

   @subject = 'Your '+@monthlyaccountdescription+' Program at the Wash' ,


If that doesn't work, then I think you will need to create a separate variable for it:

declare @subject varchar(255)
select  @subject = 'Your '+@monthlyaccountdescription+' Program at the Wash'

--Sending Mail
    EXEC msdb.dbo.sp_send_dbmail @recipients = @recipientemail,
          @subject,
          @body = '<p>Dear Neil,</p>
                                   <p>Your credit card ending with an expiration date of DDYY has been charged in the amount of $49.95 for the Program.</p>
                                   <p>Thank you for your business.</p>',
          @body_format = 'HTML';
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
I confirm: you cannot use expression in procedure calls.
DECLARE @tmp_subject varchar(1000)

WHILE @@FETCH_STATUS = 0
   BEGIN
    SET @tmp_subject = 'Your '+@monthlyaccountdescription+' Program at the Wash'

    --Sending Mail
    EXEC msdb.dbo.sp_send_dbmail @recipients = @recipientemail,
          @subject = @tmp_subject
,           @body = '<p>Dear Neil,</p>
				   <p>Your credit card ending with an expiration date of DDYY has been charged in the amount of $49.95 for the Program.</p>
				   <p>Thank you for your business.</p>',
          @body_format = 'HTML';
             
      FETCH NEXT FROM Mail_Cursor
      INTO @recipients, @subject, @body;
   END;

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial