Problem
When assigning variables in T-SQL, what is the difference between the SET and SELECT statements?
Asked by juur
Solution #1
This is a quote from the article that summarizes it:
Answered by OMG Ponies
Solution #2
SET, in my opinion, is an ANSI standard, whereas SELECT is not. In the example below, notice how SET and SELECT behave differently when a value isn’t found.
declare @var varchar(20)
set @var = 'Joe'
set @var = (select name from master.sys.tables where name = 'qwerty')
select @var /* @var is now NULL */
set @var = 'Joe'
select @var = name from master.sys.tables where name = 'qwerty'
select @var /* @var is still equal to 'Joe' */
Answered by Joe Stefanelli
Solution #3
This distinction should be kept in mind when crafting queries:
DECLARE @A INT = 2
SELECT @A = TBL.A
FROM ( SELECT 1 A ) TBL
WHERE 1 = 2
SELECT @A
/* @A is 2*/
---------------------------------------------------------------
DECLARE @A INT = 2
SET @A = (
SELECT TBL.A
FROM ( SELECT 1 A) TBL
WHERE 1 = 2
)
SELECT @A
/* @A is null*/
Answered by GorkemHalulu
Solution #4
Aside from ANSI and speed, there is a significant difference that I consider to be more important than ANSI and speed. I’ve repaired a lot of bugs as a result of this critical oversight. During code reviews, I am constantly on the lookout for this.
-- Arrange
create table Employee (EmployeeId int);
insert into dbo.Employee values (1);
insert into dbo.Employee values (2);
insert into dbo.Employee values (3);
-- Act
declare @employeeId int;
select @employeeId = e.EmployeeId from dbo.Employee e;
-- Assert
-- This will print 3, the last EmployeeId from the query (an arbitrary value)
-- Almost always, this is not what the developer was intending.
print @employeeId;
Almost never is this the intention of the developer. The query in the example above is simple, but I’ve seen searches that are fairly sophisticated, and determining whether they will return a single response or not is not easy. The query is often more complex than this and by chance it has been returning single value. During developer testing all is fine. But this is like a ticking bomb and will cause issues when the query returns multiple results. Why? Because it will simply assign the last value to the variable.
Let’s do the same thing with SET now:
-- Act
set @employeeId = (select e.EmployeeId from dbo.Employee e);
You will receive the following error:
That’s incredible and crucial, because why would you want to assign the @employeeId to some insignificant “last item in result”? You will never get an error with choose, and you will spend minutes, if not hours, debugging.
SET will force you to fix your query if you’re looking for a single Id. As an example, you could do the following:
-- Act
-- Notice the where clause
set @employeeId = (select e.EmployeeId from dbo.Employee e where e.EmployeeId = 1);
print @employeeId;
Cleanup
drop table Employee;
In conclusion, use:
Answered by CodingYoshi
Post is based on https://stackoverflow.com/questions/3945361/set-versus-select-when-assigning-variables