Both the SET command and the SELECT
command can assign the value of an expression to a variable. The main
difference between the two is that a SELECT can retrieve data from a data
source (for example, table, sub query, or view) and can include the other
SELECT clauses as well (for example, FROM, WHERE), whereas a SET is limited to
retrieving data from expressions.
See the example
USE AdventureWorks2012_CS
DECLARE @ProductID int,
@ProductName
varchar(25);
SET @ProductID = 782;
print 'Using Set Product ID : '+ cast(@ProductID as varchar(100))
SELECT
@ProductID = ProductID,
@ProductName
= Name
FROM Production.Product
ORDER BY ProductID;
SELECT @ProductID as ProductID, @ProductName as ProductName;
|
See the output
Both SET and SELECT can include
functions. Use the simpler SET command when we need to assign only a function
result or constant to a variable and don’t need the Query Optimizer to consider
a data source. Additionally, SET can only set the value of one variable, while
multiple variable values may be set with a single SELECT statement.
If the SELECT statement retrieves
multiple rows, then the values from the last row are stored in the variables.
No error will be reported.
See the example
Last value of the select
statement
Now I am assigning this value in a
variable and see the result.
USE AdventureWorks2012_CS
DECLARE @ProductID int,
@ProductName
varchar(25);
SELECT
@ProductID = ProductID,
@ProductName
= Name
FROM Production.Product
ORDER BY ProductID
desc;
SELECT @ProductID as ProductID, @ProductName as ProductName;
|
If the select statement did not return
any value the variable value will be null. See the example
