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.
Setting Variable Values
In the SQL Server Programming Fundamentals tutorial I used variables in several articles, assigning values to the variables with several techniques. Sometimes they were set to specific values or the results of calculations using the Transact-SQL (T-SQL) SET statement. Other times they were defined as parameters of a stored procedure and received values from the arguments used when calling the procedure. On yet other occasions values were assigned from the results returned by a query.
In most cases when you wish to assign a discrete value to a variable, where that variable is not a parameter, you will use the SET statement. You simply name the variable to be altered after the SET keyword and provide the value, or calculation to generate a value, after an equals sign (=).
The following example shows this by declaring a variable, assigning a value with SET and displaying the value using the PRINT statement. Try executing the script in SQL Server Management Studio to see the results.
DECLARE @Name VARCHAR(10)
SET @Name = 'BlackWasp'
PRINT @Name
/* OUTPUT
BlackWasp
*/
Assigning Variables from Query Results
The focus of this article is setting variable values with results from queries. This allows you to gather information from tables or views and store it in a variable temporarily. We've seen this in the aforementioned tutorial but I didn't describe the technique in detail in those articles.
To demonstrate the possibilities we will need a table with some sample data. Create a new test database and execute the following script against it to create the table and data. This table might be used in a company database to store the names of all employees and the number of years of experience that they have accrued:
CREATE TABLE People
(
Name VARCHAR(10),
ExperienceYears TINYINT
)
INSERT INTO People VALUES ('Bob', 5)
INSERT INTO People VALUES ('Sam', 10)
INSERT INTO People VALUES ('Mel', 20)
INSERT INTO People VALUES ('Jim', 25)
INSERT INTO People VALUES ('Kat', 15)Simple Assignment from a Query
You can use the SELECT statement to execute a query and set a variable's value to a returned value simply by adding the name of the variable and an equals sign before the column that holds the value. The syntax for a basic operation shown below:
SELECT @variablename = column-name FROM table
A possible use for this is to perform an aggregation upon the data and store the aggregated value in the variable. You can see this in the sample below, which totals the experience of all of the employees.
DECLARE @TotalExperience INT
SELECT @TotalExperience = sum(ExperienceYears) FROM People
PRINT @TotalExperience
/* OUTPUT
75
*/
Multiple Assignments
When you wish to assign values to more than one variable and the desired information can be retrieved using a single query, you can use multiple assignments in your statement. Simply add the variable names and equals signs before each of the relevant columns.
For example, the following obtains the total experience and average experience for all of the employees using one query.
DECLARE @TotalExperience INT, @AverageExperience INT
SELECT
@TotalExperience = sum(ExperienceYears),
@AverageExperience = avg(ExperienceYears)
FROM
People
PRINT @TotalExperience
PRINT @AverageExperience
/* OUTPUT
75
15
*/28 July 2013