How to initialize several SQL Server variables with values stored in a temp table.

Suppose we have the following temporary table in SQL Server:

CREATE TABLE #myvalues (val int)

populated with values:

INSERT INTO #myvalues 
VALUES
(10), (15), (11), (18), (22)

We want to store these values in different variables like @v1, @v2, @v3, @v4, @v5. Is it possible to do so in a single SQL query? The answer is yes.

Consider the following query using ROW_NUMBER:

SELECT val, 
       ROW_NUMBER() OVER (ORDER BY val) AS rn
FROM #myvalues

it yields this output:

val	rn
10	1
11	2
15	3
18	4
22	5

So, what the query essentially does is that it assigns a distinct number to each record of the temporary table.

We can now use a technique called conditional aggregation on the output produced by the previous query, in order to set the values of all 5 variables with a single query:

SELECT @v1 = MAX(CASE WHEN rn = 1 THEN val END),
       @v2 = MAX(CASE WHEN rn = 2 THEN val END),
       @v3 = MAX(CASE WHEN rn = 3 THEN val END),
       @v4 = MAX(CASE WHEN rn = 4 THEN val END),
       @v5 = MAX(CASE WHEN rn = 5 THEN val END)
FROM (
   SELECT val, ROW_NUMBER() OVER (ORDER BY val) AS rn
   FROM #myvalues ) t

This technique uses a CASE expression inside an aggregate function, like MAX in our case. Each usage of the conditional aggregate produces a separate field that corresponds to a distinct record of the temporary table.

So, for example,

@v1 = MAX(CASE WHEN rn = 1 THEN val END)

assigns @v1 the value of the first record of the table.

Click here for a live demo.