Set Variable From Query Results

[mcfly1204]

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.

[deepuv04]

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]

[mcfly1204]

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

[ck9663]

One catch (there might be some that I don't know about), Deepuv writes:

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