BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

Selecting into Variables in Transact-SQL

Variables are essential to the operation of many stored procedures, as they allow the temporary storage of values calculated using the operators and functions provided by Transact-SQL. Assigning variable values from query results is a common technique.

Assignment from Multiple Result Rows

The operation of the above examples is reasonably obvious, as the queries have only generated a single row of data and this has been the source for assigned values. However, it's quite possible that a query will return many more rows. It's important to know what happens in these cases so that you do not get unexpected results in your variable assignments.

Let's try an example. The query below retrieves all of the employees' names. There are five results but the variable cannot hold them all. In this case the variable is assigned the value, "Kat". This result seems rather arbitrary.

DECLARE @Name VARCHAR(10)
SELECT @Name = Name FROM People
PRINT @Name

/* OUTPUT

Kat

*/

Why did we end up with "Kat" as the result? When you assign a variable from a query with multiple rows, you should think about the process as a series of assignments. Imagine that every row's value is applied to the variable in turn, overwriting the earlier value. In our example, "Kat" happens to be the final result of the query so is applied last and is not overwritten.

You can see this by ordering the results of the query. In the script below we order by the amount of experience attained by each employee. The person with the most experience will be the last result and, therefore, the value assigned to the variable. This is Jim, who has twenty-five years of experience.

DECLARE @Name VARCHAR(10)
SELECT @Name = Name FROM People ORDER BY ExperienceYears
PRINT @Name

/* OUTPUT

Jim

*/

Combining Results

In some cases you might want to combine information from all of the rows returned by a query and store a single result in a variable. For example, you might wish to generate a string containing comma-separated values (CSV) from a particular column. In the case of our example data, perhaps you would want a CSV of the names.

This is possible by setting the new value of the variable to a value that includes the existing data. As the result rows are processed, the variable's value will be repeatedly modified, rather than being replaced over and over again.

For example, consider the following query:

DECLARE @Names VARCHAR(MAX)
SELECT @Names = CASE
    WHEN @Names IS NULL THEN Name
    ELSE @Names + ', ' + Name
    END
FROM People ORDER BY Name
PRINT @Names

/* OUTPUT

Bob, Jim, Kat, Mel, Sam

*/

This query checks the current value of @Names using a CASE expression. If the value is null, the variable is assigned the name from the current row. If the variable already has a value, the variable is assigned that value with a comma, space and the next name appended.

For the first row, the name returned is "Bob" and the variable has not yet been assigned, so is null. This means that the first assigned value will be "Bob". For the second row, the variable already has the value, "Bob". The new value is therefore "Bob", with the comma, space and the next name appended. This gives, "Bob, Jim". The process repeats for the remaining rows to give the end result shown in the comment.

As a final note, if you have read the "Using T-SQL's COALESCE Function" article, you might have spotted that the CASE statement in the above example can be replaced with COALESCE. This means that you can simplify the CSV generation query, as follows:

DECLARE @Names VARCHAR(MAX)
SELECT @Names = COALESCE(@Names + ', ', '') + Name
FROM People ORDER BY Name
PRINT @Names

/* OUTPUT

Bob, Jim, Kat, Mel, Sam

*/
28 July 2013