SET VS SELECT

There are a couple of differences between using SET or SELECT statement when it comes to assigning values to local variables. Some are trivial and others may cause bugs that are hard to find in your code;

One of the issues that you need to be aware of is when using SELECT to assign value to a variable, if the select returns more than one record, it will assign one of the returned values to your local variable which may not be what you want.

But, SET will return an error if there are more than one records returned which in my openion is a better option. Here is a demo;


DECLARE @Id INT
DECLARE @Temp TABLE (ID INT , NAME VARCHAR(25))

INSERT INTO @temp (id, name) VALUES (1,'one')
INSERT INTO @temp (id, name) VALUES (2,'one')
INSERT INTO @temp (id, name) VALUES (3,'Two')
INSERT INTO @temp (id, name) VALUES (4,'Three')

-- With select you dont get an error

SELECT @Id =id FROM @Temp WHERE Name='one'
SELECT @Id AS RETURNS_one_of_the_records_Without_any_errors

--You get NO errors, and ONE of the records is returned which may not be what you want.

-- With SET you will get an error if there are more than one records returned
SET @Id =(SELECT id FROM @Temp WHERE Name='one')

--You get error, Which is better
SELECT @Id AS RETURNS_errors_as_There_Are_More_than_one_records

In the SET statement, you can check for @@Error and handle accordingly or if you have to use SELECT, you may be able to pick the record that you need by using TOP 1 and order by in the SELECT statement.

The second issue is when the SELECT returns NULL, it does not assign the NULL to the variable. So if your variable had a value of X before the SELECT statement was executed, and the SELECT returned NULL, your variable value will still be X as shown below.


DECLARE @Id INT
DECLARE @Temp TABLE (ID INT , NAME VARCHAR(25))

-- Assume @Id had a vlaue of 100
SET @Id =100

-- Get the new value of @id from a table using SELECT
SELECT @Id =id FROM @Temp WHERE Name='Does not exist'

--You get wrong value. the value of @id should be Null and not 100
SELECT @Id AS Id_Value_Should_be_Null

SET @Id =(SELECT id FROM @Temp WHERE Name='Does not exist')

--This will show correct value i.e Null
SELECT @Id AS Id_Value_Should_be_Null
SELECT @Id =(SELECT id FROM @Temp WHERE Name='Does not exist')

--This will show correct value i.e. null
SELECT @Id AS Id_Value_Should_be_Null

Hopefully knowing these settle differences will save you time debugging !