Setting value to a variable by using SELECT or SET statement

You can assign value to a variable by using SET or SELECT statement and almost they work the same except that a SELECT statement has the ability to have the source value come from a column within the SELECT statement.


Let's see these examples to clarify the difference between the two statements:

USE AdventureWorks
GO

DECLARE @Res DateTime
-- Assign value to a variable by using SET statement
SET @Res = GETDATE();
SELECT @Res

SET @Res = (SELECT ModifiedDate FROM Person.Contact WHERE ContactID = 1)
SELECT @Res
-------------------------------------------------------------------------
--Just this case is show the diff between the SET and SELECT
-- It will cause an error
SET @Res = ModifiedDate FROM Person.Contact WHERE ContactID = 1
SELECT @Res
-------------------------------------------------------------------------
-------------------------------------------------------------------------
-- Assign value to a variable by using SELECT statement
SELECT @Res = GETDATE();
SELECT @Res

SELECT @Res = (SELECT ModifiedDate FROM Person.Contact WHERE ContactID = 1)
SELECT @Res
--------------------------------------------------------------------------
-- It will success
SELECT @Res = ModifiedDate FROM Person.Contact WHERE ContactID = 1
SELECT @Res
--------------------------------------------------------------------------