The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 2,570 other followers

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:

  1. 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.
  2. You can use SELECT without a FROM clause: that returns one row with just the expressions mentioned after the SELECT clause.
  3. 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:

  1. SET is the ANSI standard for variable assignment, SELECT is not.
  2. (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.
  3. 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.
  4. (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.

  5. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

 
%d bloggers like this: