By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,460 Members | 1,138 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,460 IT Pros & Developers. It's quick & easy.

Set Variable From Query Results

100+
P: 233
I have a stored procedure with a query that selects multiple values, each of which I would like to assign to local variables. I am aware of how to set a single local variable by way of SET @variable1 = SELECT * FROM TABLE WHERE Column = 'unique value', but as I said, this only works for single variables.

My initial thoughts were if it were possible to do something such as:

DECLARE @variable1, @variable2

SELECT column1, column2
FROM table1
WHERE column1 = 'unique value'

SET @variable1 = column1
SET @variable2 = column2

Obviously this does not work, but I am hoping that there is a method to accomplish something like this.
Mar 5 '08 #1
Share this Question
Share on Google+
3 Replies


deepuv04
Expert 100+
P: 227
I have a stored procedure with a query that selects multiple values, each of which I would like to assign to local variables. I am aware of how to set a single local variable by way of SET @variable1 = SELECT * FROM TABLE WHERE Column = 'unique value', but as I said, this only works for single variables.

My initial thoughts were if it were possible to do something such as:

DECLARE @variable1, @variable2

SELECT column1, column2
FROM table1
WHERE column1 = 'unique value'

SET @variable1 = column1
SET @variable2 = column2

Obviously this does not work, but I am hoping that there is a method to accomplish something like this.
[cdoe = sql]

you can assign values to multiple variables as
SELECT @variable1 = column1, variable2 = column2
FROM table1
WHERE column1 = 'unique value'


[/code]
Mar 5 '08 #2

100+
P: 233
[cdoe = sql]

you can assign values to multiple variables as
SELECT @variable1 = column1, variable2 = column2
FROM table1
WHERE column1 = 'unique value'


[/code]
You know, I just read an article about the differences between set and select, and I read about how set cannot assign multiple variables values, but select can. I have no idea why this did not click. Thanks for the response though.
Mar 5 '08 #3

ck9663
Expert 2.5K+
P: 2,878
One catch (there might be some that I don't know about), Deepuv writes:
Expand|Select|Wrap|Line Numbers
  1. WHERE column1 = 'unique value'
on the code because he's stressing your query should return only one row. Otherwise, you might have unexpected values on your variables.

-- CK
Mar 5 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.