How to store multiple values from SELECT in SQL Server?

Question

Is there a way to store the results of a SELECT query in a stored proc using SQL Server?

In the example below I would like to store both "one", and "two" from the select query.

Example:

DECLARE @Values ???
SET @Values = (SELECT one, two FROM dummyTable)

You wish to grab multiple values from a single row or many rows? — Tim Lehner
– Tim Lehner, Commented Jan 3, 2012 at 20:27

JNK · Accepted Answer · 2012-01-03 20:05:43Z

5

You can use a table variable for this:

DECLARE @Table TABLE (one varchar(100), two varchar(100)

INSERT INTO @TABLE
SELECT one, two from dummytable

Like other variables, this won't be accessible from outside the scope of the proc.

It's also not good for large numbers of rows since the optimizer always assumes a single row for execution plans.

answered Jan 3, 2012 at 20:05

JNK

65.5k15 gold badges127 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Key_coder · Accepted Answer · 2014-11-05 13:45:04Z

4

SELECT @Value1 = One, @Value2 = two FROM dummyTable;

You don't have any arrays in SQL Server.

Key_coder

5363 silver badges27 bronze badges

answered Jan 3, 2012 at 20:05

gbn

434k84 gold badges602 silver badges690 bronze badges

This is a very useful technique

RickNZ · Accepted Answer · 2012-01-03 23:29:31Z

2

If your query only returns a single row:

DECLARE @v1 int -- or whatever
DECLARE @v2 int
SELECT @v1 = one, @v2 = two
  FROM dummyTable
  WHERE id = 1

If your query returns multiple rows, you can use SELECT INTO to avoid having to declare a table variable:

SELECT one, two
  FROM dummyTable
  INTO #temp
SELECT * FROM #temp
DROP TABLE #temp

answered Jan 3, 2012 at 23:29

RickNZ

18.7k3 gold badges55 silver badges67 bronze badges