We help IT Professionals succeed at work.

[simple Q] Assign the result of a SELECT query to a variable

Medium Priority
791 Views
Last Modified: 2008-08-10
Hi everybody!

I am new to stored procedures/functions and I have a simple question:

How can I assign the result of a SELECT query to a variable?
The query is like:

SELECT  text FROM OPENXML(@hdoc, '/', 1) WHERE text IS NOT NULL AND parentid=0


Thank you!

Comment
Watch Question

CERTIFIED EXPERT
Commented:
set @tmp = (SELECT  text FROM OPENXML(@hdoc, '/', 1) WHERE text IS NOT NULL AND parentid=0)

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
SELECT  @variable = text FROM OPENXML(@hdoc, '/', 1) WHERE text IS NOT NULL AND parentid=0

Author

Commented:
Thank you both for replying!

The parenthesis did the trick

This works as well

SELECT @rslt = (SELECT  text FROM OPENXML(@hdoc, '/') WHERE text IS NOT NULL AND parentid=0)
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
try mine too, that's the better option

Author

Commented:
On MSS2005 it doesn't work without the parenthesis.
I don't know why but I get the following error message:

Msg 156, Level 15, State 1, Procedure spMyParseXML, Line 20
Incorrect syntax near the keyword 'SELECT'.

Author

Commented:
>try mine too, that's the better option

BTW why do you think so?
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
in case  '(SELECT  text FROM OPENXML(@hdoc, '/', 1) WHERE text IS NOT NULL AND parentid=0) ' statement is returning more that one values , it will throw an error with the SET statement, because it works as a SubQuery. meanwhile , in case of the second method, it wont throw an error, instead it stors the last amongg those values.

Author

Commented:
aneeshattingal, thank you for the clarification.


My query is supposed to aways return only one value and that's why
I wanted to assign it to a variable. This should be (kind of) enforced by the
WHERE clause.

I just tried to run it without the WHERE clause and I got the following error:

Msg 512, Level 16, State 1, Procedure spMyParseXML, Line 20
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.


this is the query without the WHERE clause:

        -- the SELECT query returns 2 fields
      SELECT @rslt =( SELECT  text FROM OPENXML(@hdoc, '/') )

Correct me if I'm wrong but on MS SQL Server 2005
it looks like it doesn't make any difference whether I use SELECT or SET.

Author

Commented:
I just came across this article:
http://ryanfarley.com/blog/archive/2004/03/01/390.aspx

May be I should start new thread about it.

Thank you once again.
Database Consultant
CERTIFIED EXPERT
Top Expert 2009
Commented:
Just check my post, i am not using any sort of SubQuery
SELECT @rslt = text FROM OPENXML(@hdoc, '/')

Author

Commented:
Yep it works.
I'll split the points.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*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.