SQLTeam.com | Weblogs | Forums

Incorrect syntax near "+"


#1

Greetings experts.

Hopefully, there is a simple solution to this problem.

This stored procedure is intended to grab changes stored on the database where IsOpen = 0

Then email these changes to users.

When attempting to run the code below, I keep getting error:

Incorrect syntax near '+'.

which is on this code snip:

@body = 'This is a computer generated email message. Please DO NOT use the REPLY button above to respond to this email. Dear '+@authorizedname+':

Complete code below.

create proc [dbo].[SendUpdatedRecords] as DECLARE MAIL_CURSOR CURSOR FOR SELECT top 1 [ID] ,[Sender] ,[Department] ,[Bldg] ,[DateCreated] ,[RequestID] ,[Email] ,[InitiatedBy] FROM CompletedOrder WHERE Status = 'Closed' Declare @mailid int Declare @sender nvarchar(200) declare @dept nvarchar(200) declare @dteCreated nvarchar(20) declare @RequestID nvarchar(12) declare @email nvarchar(200) declare @authorizedname nvarchar(200) OPEN MAIL_CURSOR FETCH MAIL_CURSOR into @mailid, @sender WHILE @@FETCH_STATUS = 0 BEGIN SELECT @email = @email+';'+Email FROM CompletedOrder WHERE lsOpen = 0 --exec sp_send_cdontsmail @mail1, null,null,@content1,null EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Feedback Survey', -- your defined email profile or static info @recipients = @email, -- your email --@blind_copy_recipients = @email, @subject = 'Your feedback is needed', @body = 'This is a computer generated email message. Please DO NOT use the REPLY button above to respond to this email. Dear '+@authorizedname+': Thank you for using Service Request app. Please click the link below to take a brief survey to let us know about your experience using our service request app. http://folder/satisfactionsurvey.php?wo=@RequestID ' --Update the record in ClosedRequest table where Sent = 'No'. Update CompletedOrderSET lsOpen = 1 WHERE lsOpen = 0 and ID = @mailid FETCH MAIL_CURSOR INTO @mailid, @sender END CLOSE MAIL_CURSOR DEALLOCATE MAIL_CURSOR Thanks for your help.

Sorry I am using my cell and I can't seem to figure out sql formatting code.


#2

the error is at the @body

you can NOT perform string concatenation directly when calling a stored procedure.

declare a variable, concatenate the string there before passing to the stored procedure

declare @email_body varchar(1000)

select @email_body  = ''This is a computer generated email message. Please DO NOT use the REPLY button above to respond to this email. Dear '+@authorizedname+': ....'

exec msdb.dbo.sp_send_dbmail @profile_name = 'Feedback Survey',  .... @body  = @email_body

#3

Thank you very much khtan.


#4

You can surround your code with:

[code]
...
[/code]

:smile:

I think you meant "not"? :smile:


#5

thanks :smile: