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
error131 asked
on
224 Views
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
as
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;'
end;

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.
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2012

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

Author

Commented:
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.

CERTIFIED EXPERT
Top Expert 2012

Commented:
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.

Author

Commented:
Sure.
declare @username nvarchar(50)
      set @username = 'amcat user'

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

output:
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.

CERTIFIED EXPERT
Top Expert 2012
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Top Expert 2012

Commented:
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?
https://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi73

And specifically this section:

<quote>
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.
</quote>

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?
https://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi18
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.

OR

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.