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)

Solution 1

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

You don't have any arrays in SQL Server.

Solution 2

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.

Solution 3

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