Using the Set and Select Commands assign the value of a variable

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