SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Storing a variable with a SELECT, and why it didn't work.

By Kenneth Fisher,

One of the ways to set the value of a variable is with a SELECT statement. This has the benefit of being able to set multiple variables at once.

DECLARE @create_date datetime = '1/1/1900'
DECLARE @database_id int = 0

SELECT @create_date = create_date, 
     @database_id = database_id
FROM sys.databases
WHERE name = 'master'

SELECT @create_date AS Create_Date, @database_id AS Database_Id

This is a very useful technique but you do have to be careful. What happens can vary depending on the number of rows you get back from the query.

One row

This is the best case and will act exactly like you expect. The value for each variable will be the specified values from the row returned.

Multiple rows

If you have multiple rows being returned then the values stored in the variables will be from the <em>last</em> row returned from the query.  

DECLARE @create_date datetime = '1/1/1900'
DECLARE @database_id int = 0

SELECT @create_date = create_date, 
     @database_id = database_id
FROM sys.databases

SELECT @create_date AS Create_Date, @database_id AS Database_Id

The problem here is that last is pretty uncertain unless you put an ORDER BY into the query. Remember that without an ORDER BY, a query has no guaranteed order. It might be in the same order hundreds of times and then change for some reason (a new index was added, SQL was updated, or something even less obvious).

No rows

This one can get really confusing if you aren't aware it can happen. If your query returns no rows then the variables will remain unchanged.

DECLARE @create_date datetime = '1/1/1900'
DECLARE @database_id int = 0

SELECT @create_date = create_date, 
     @database_id = database_id
FROM sys.databases
WHERE 1=0

SELECT @create_date AS Create_Date, @database_id AS Database_Id

You might think that you could change the query to use ISNULL to avoid the problem:

SELECT @create_date = ISNULL(create_date,'12/31/2099'), 
@database_id = ISNULL(database_id,-1)
FROM sys.databases
WHERE 1=0

But this doesn't help. The problem is that without any rows being returned no update will happen. Putting an ISNULL around the result (or any other functions you try to use) will only make a difference if a row is actually being returned. There best way to avoid this particular problem is to initializing your variables ahead of time. Also if you are resetting the variable multiple times make sure that you reinitialize it within each code block. For example:

DECLARE @create_date datetime = '1/1/1900'
DECLARE @database_id int = 0

SELECT @create_date = create_date, 
     @database_id = database_id
FROM sys.databases
WHERE name = 'master'

/* Code goes here */

SET @create_date = '1/1/1900'
SET @database_id = 0

SELECT @create_date = create_date, 
     @database_id = database_id
FROM sys.databases
WHERE name = 'SSISDB'

/* Code goes here */

In the code above I'm pulling the information for master, then I'm doing some processing on that information. Next, I'm pulling information about SSISDB and processing it. If I don't reinitialize the variables and don't happen to have an SSISDB then I'm going to reprocess master.

Using SELECT to store values into a variable is something you will have to know how to do if you are doing much coding in T-SQL. But you also have to know how it reacts and how to avoid problems.

 
Total article views: 1793 | Views in the last 30 days: 24
 
Related Articles
FORUM

Variable Declaration

Variable Declaration

FORUM

Must declare the table variable

Must declare the table variable

FORUM

declaring global variables

declaring global variables

FORUM

variable columns in select statements?

want to create select's with variable column names

FORUM

Must declare the scalar variable

Must declare the scalar variable "@sunday"

Tags
select    
t-sql    
 
Contribute