We help IT Professionals succeed at work.

I need to use a variable in a SQL mail query but I am getting an error message.

error131 asked
Last Modified: 2008-03-10
I don't know what I am doing wrong here. In books online I shows you a limmited example of how to use a variable but nothing that uses them in a query. My query is below.
alter procedure usp_email
declare @username nvarchar(50)
select @Username = db..users."name" from db..users
where db..users."name" = 'value'
if (@username = 'value')begin
    EXEC master.dbo.xp_sendmail 'email@email.com',
      @Query = @username, 'select db..users."name" from db..users where db..users."name" = @username;'

The error I get is:
Server: Msg 119, Level 15, State 1, Procedure usp_email, Line 9
Must pass parameter number 3 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.
can anyone give me any hints to what I am doing wrong? Or if I can even do this in this way.
Watch Question

Top Expert 2012

You will need to place that in a cursor and execute xp_sendmail for each row.


But it only retuns one row. So I am unclear how a cursor would help me here.

The real question I should have asked is how do I pass a parameter into the @Query = 'string'?  

I should also say that this query is just a test for a much larger stored procedure so it is not by any means exact.

Top Expert 2012

I am having a hard time understanding what you are trying to achieve.  Any chance you can give us the select query without adding the xp_sendmail?  Also a sample of the output.


declare @username nvarchar(50)
      set @username = 'amcat user'

select "name", Login, "Password", IsEmail, Extension from users where "name" = @username

Amcat User        amcat     1           0      

Ultumatly I will be passing a prameter to a stored proc to give the value for @username but here I have just assigned the value.

Top Expert 2012
This one is on us!
(Get your first solution completely free - no credit card required)
Top Expert 2012

I realize you are new here so I will merely suggest you take the time to re-read the EE Guidelines regarding grading standards at:

What's the right grade to give?

And specifically this section:

C: Because Experts' reliability are often judged by their grading records, many Experts would like the opportunity to clarify if you have questions about their solutions. If you have given the Expert(s) ample time to respond to your clarification posts and you have responded to each of their posts providing requested information; or if the answers, after clarification, lack finality or do not completely address the issue presented, then a "C" grade is an option. You also have the option here of just asking Community Support to delete the question.

Remember, the Expert helping you today is probably going to be helping you next time you post a question. Give them a fair chance to earn an 'Excellent!' grade and they'll provide you with some amazing support. It's also true that a "C" is the lowest grade you can give, and the Experts know that -- so use it judiciously.

Also, this link is helpful in case you do not want to leave the "C" grade in your history for all to see in future:
Can I get a grade changed?
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.