Difference between SET and SELECT while assigning some value

Posted by Bandi under Sql Server category on | Points: 40 | Views : 318
SET statement is useful when the query returns only single value; SELECT can also be used to assign some value to a variable.

If the result from a query returns more than one value, then SELECT holds last value of the result for the variable; whereas SET throws exception...

Sample code
Basic usage of SET & SELECT for assigning variable value,
create table #t(id int, names varchar(100))
insert into #t(id,names)
select 1,'test1' union all
select 2,'test2' union all
select 3,'test3' union all
select 4,'test4' union all
select 5,'test5'

Here, select returns SINGLE value, so the result both queries ( SET vs SELECT) is same..
declare @id int
set @id=(select id from #t where names='test1')
select @id as id -- 1

select @id=id from #t where names='test1'
select @id as id -- 1

What if the query retuns multiple values for variable ?

-- Using SELECT for variable assignment
declare @id1 int
SELECT @Id1 = id from #t)

Result is last ID value in table..


-- Using SET for variable assignment
declare @id1 int
set @id1=(select id from #t)

ERROR message

Msg 512, Level 16, State 1, Line 19
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.


Note: Always make sure that the SELECT query returns single value when you write code that assigns value to a variable. Otherwsie the variable will have wrong/unexpected data

Comments or Responses

Login to post response