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
--------------------------------------------