SQL Server: SELECT multiple declared variables
Posted by jpluimers on 2010/07/14
Declared @local_variables and parameters in stored procedures are a very handy feature of SQL Server.
They allow for local storage, and for passing information around without storing them in your database first.
You can assign values by using both SET @local_variable, and SELECT @local_variable.
Note that these are different than a regular SET or regular SELECT.
In addition, SET @local_variable and SELECT @local_variable are different too.
Though Microsoft recommends using SET, there are a differences between SET and SELECT, which can make SELECT a lot faster in loops.
Assigning multiple @local_variables in a single SELECT query
There seem to be no examples (or very few: as I could not find them) about using the SELECT @local_variable to select values into multiple @local_variables from a query.
This can be very useful, so here is a small example:
declare @name varchar(20), @filename varchar(260); select @name = name, @filename = filename from sys.sysdatabases where dbid <= 4 ; select @name, @filename ; select dbid, name, filename from sys.sysdatabases where dbid <= 4 ;
The output of @name and @filename (since the colums are not named in the final SELECT, they appear as COLUMN1 and COLUMN2):
COLUMN1 COLUMN2 msdb c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBData.mdf
The output of dbid, name and filename:
dbid name filename 1 master c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf 2 tempdb c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\tempdb.mdf 3 model c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\model.mdf 4 msdb c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBData.mdf
The above piece of code declares 2 variables (@name and @filename), then fills them from sys.databases using the name and filename columns.
This is what happens in the above code:
- sys.databases is not a singleton table (table having only one row, like the DUAL table in Oracle and MySQL, the RDB$DATABASE table in Firebird/InterBase, the SYBASE DUMMY table, and sometimes the inserted or deleted pseudo tables in SQL Server).
Therefore the query is not a singleton query.
So you might the SELECT to fail (since there is only one instance of @namd and @filename).
But SELECT fills the values of @name and @filename from the last record returned from sys.database.
In this particular case, SET would fail. - You can use SELECT without a FROM clause: that returns one row with just the expressions mentioned after the SELECT clause.
- sys.databases indeed returns multiple rows.
Lets explain this further.
The above points at a few things that are easier to understand with a few examples.
SET fails when you don’t have a singleton query
declare @name varchar(20), @filename varchar(260);
set
@name =
(
select
name
from
sys.sysdatabases
)
set
@filename =
(
select
filename
from
sys.sysdatabases
)
;
select @name, @filename
;
The error message output:
Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Msg 512, Level 16, State 1, Line 10
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
SET with a singleton query
SET requires a singleton query; you need one SET per assignment, so you often have to repeat pars of queries:
declare @name varchar(20), @filename varchar(260);
set
@name =
(
select
name
from
sys.sysdatabases
where
dbid = 1
)
set
@filename =
(
select
filename
from
sys.sysdatabases
where
dbid = 1
)
;
select @name, @filename
;
The output of @name and @filename:
COLUMN1 COLUMN2 master c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
SELECT with a singleton query: easier to maintain
A singleton SELECT query assigning the @local_variables is a lot faster and easier to maintain, and gives the same result:
declare @name varchar(20), @filename varchar(260); select @name = name, @filename = filename from sys.sysdatabases where dbid = 1 ; select @name, @filename ;
The output of @name and @filename:
COLUMN1 COLUMN2 master c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
Some more differences
There are more differences between SET and SELECT for assigning @local_variables.
Prasanti Chikul (aka KnowledgeParlour) wrote a great article about differences between SET @local_variable and SELECT @local_variable:
- SET is the ANSI standard for variable assignment, SELECT is not.
- (already covered) SELECT can be used to assign values to more than one variable at a time, Whereas SET allows to assign data to only one variable at a time.
- When assigning from a query if there is no value returned then SET will assign NULL, where SELECT will not make the assignment at all .so the variable will not be changed from it’s previous value.
- (already covered) Let using a query needs to populate a variable and the Query returns more than one value.
SET will fail with an error in this scenario.
But SELECT will assign one of the returned rows and mask the fact that the query returned more than one row.As a result, bugs in your the could go unnoticed with SELECT, and this type of bugs is hard to track down too.
- A single SELECT statement assigning values to 3 different variables, is much faster than 3 different SET statements assigning values to 3 different variables.
Hope this has shed some light on using @local_variables together with SELECT.
–jeroen
PS: Singleton queries come at a cost, I’ll probably blog in more detail about singleton queries another time.
The Wiert Corner - irregular stream of stuff 
Leave a Reply