Error when executing query with variables in sp_send_dbmail

Question

I am trying to send emails individually to a list of recipients. I am receiving the error:

Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 478
Query execution failed: Msg 4104, Level 16, State 1, Server xxxxxx, Line 1
The multi-part identifier "[email protected]" could not be bound.

Here is a simplified version of my code, asssuming table1 is a valid existing table and name and email are existing columns.

declare @current_mailaddress varchar(50), @query varchar(1000)
set @current_mailaddress = '[email protected]'

set @query = 'select distinct name, email from table1 
              where email = ' + @current_email

exec msdb.dbo.sp_send_dbmail 
 @recipients = @current_email,
 @subject = 'test',
 @query = @query

So according to the error, the formatting (of presumably the @query) is wrong. I can't figure it out. Any ideas?

Community · Accepted Answer · 2017-05-23 11:50:58Z

2

You need to put the value of @current_email in quotes:

'SELECT ... WHERE email = ''' + @current_email + ''''

To see why, consider what your query currently looks like without it:

SELECT ... WHERE email = [email protected]

Any time you work with dynamic SQL, it's a good idea to PRINT the variable for debugging if you get a strange error; it's usually the case that the SQL string you've built is not the one you're expecting. I suggested an easy way to manage debugging code in another, unrelated answer.

edited May 23, 2017 at 11:50

CommunityBot

11 silver badge

answered May 30, 2012 at 7:03

Pondlife

16.3k6 gold badges40 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Error when executing query with variables in sp_send_dbmail

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related