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