Avatar of looknow12
looknow12

asked on 

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

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

Microsoft SQL Server

Avatar of undefined
Last Comment
Guy Hengel [angelIII / a3]
ASKER CERTIFIED SOLUTION
Avatar of knightEknight
knightEknight
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
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

Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo