T-SQL – SET vs SELECT

When assigning values to a variable, we can use SET or SELECT. SET is the ANSI standard method. If we have a set value we want to assign to a variable, either method works just as well as the other. When we are retrieving values from a table or view, then there are some differences in behavior.
In order to run through some examples, we’ll set up a temp table with 1 column and 2 rows of data.

drop table if exists #Test;
create table #Test (RecordId int not null primary key);
insert into #Test(RecordId) values (1), (2);

When selecting by the primary key, we know we’ll get one value, so things are straightforward.

declare @VarSelect int;
declare @VarSet int;

select @VarSelect = RecordId from #Test where RecordId = 1;
set @VarSet = (select RecordId from #Test where RecordId = 1);

select @VarSelect as VarSelect, @VarSet as VarSet;

Let’s try an example of retrieving a value that doesn’t exist in the table. We’ll set 0 as the default value for the variables.

declare @VarSelect int = 0;
declare @VarSet int = 0;

select @VarSelect = RecordId from #Test where RecordId = 3;
set @VarSet = (select RecordId from #Test where RecordId = 3);

select @VarSelect as VarSelect, @VarSet as VarSet;

Using the SET method will return NULL, where the SELECT will leave the default value in place. If we’re using the variable one time, this behavior may not cause issues, but reusing the variable could cause confusion.
In the following example, the first select will return 1. The second select won’t find a value, so it will also return 1.

declare @VarSelect int = 0;

select @VarSelect = RecordId from #Test where RecordId = 1;
select @VarSelect as VarSelect;

select @VarSelect = RecordId from #Test where RecordId = 3;
select @VarSelect as VarSelect;

When returning multiple values, a SET will raise this error:
“Msg 512, Level 16, State 1, Line x
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, , >= or when the subquery is used as an expression.”
The SELECT will return one of the values from the result set.

declare @VarSelect2 int = 0;
declare @VarSet2 int = 0;

select @VarSelect2 = RecordId from #Test;
set @VarSet2 = (select RecordId from #Test);

select @VarSelect2 as VarSelect, @VarSet2 as VarSet;

In this case, I would rather an error be thrown that to just return one of the result set values.
Overall, it seems like SET it the better choice. We’ll get an error if multiple values are returned, and if no value is returned then we’ll get a NULL.

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

%d bloggers like this: