[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2007-07-20
12
Medium Priority
?
762 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!

0
Comment
Question by:0xC0DEB07
  • 6
  • 4
11 Comments
 
LVL 24

Assisted Solution

by:mankowitz
mankowitz earned 800 total points
ID: 19530077
set @tmp = (SELECT  text FROM OPENXML(@hdoc, '/', 1) WHERE text IS NOT NULL AND parentid=0)
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 19530080
SELECT  @variable = text FROM OPENXML(@hdoc, '/', 1) WHERE text IS NOT NULL AND parentid=0

0
 
LVL 6

Author Comment

by:0xC0DEB07
ID: 19530110
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)
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 19530131
try mine too, that's the better option
0
 
LVL 6

Author Comment

by:0xC0DEB07
ID: 19530145
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'.

0
 
LVL 6

Author Comment

by:0xC0DEB07
ID: 19530146
>try mine too, that's the better option

BTW why do you think so?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 19530388
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.

0
 
LVL 6

Author Comment

by:0xC0DEB07
ID: 19531291
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.
0
 
LVL 6

Author Comment

by:0xC0DEB07
ID: 19531305
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.
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 1200 total points
ID: 19531321
Just check my post, i am not using any sort of SubQuery
SELECT @rslt = text FROM OPENXML(@hdoc, '/')
0
 
LVL 6

Author Comment

by:0xC0DEB07
ID: 19546695
Yep it works.
I'll split the points.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question