SET and SELECT in SQL Server

3. Third difference can be found when the query returns more than one value. In this case , SET would return an error.

For instance,the following code:

Declare  @Year_of_PassOut int SET @Year_of_PassOut= (SELECT Year_of_PassOut

FROM [GPSTrainees].[dbo].[dipali_Backup])

Would return the error-

Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, = or when the subquery is used as an expression.

However, when using the SELECT statement with a query returning multiple values, the statement would assign the last value returned and would ignore the other values.

Declare  @Year_of_PassOut int SELECT  @Year_of_PassOut = Year_of_PassOut

FROM [GPSTrainees].[dbo].[dipali_Backup]

SELECT  @Year_of_PassOut—The result will be 2005

The value returned in the code is the last value that the query returns and it is this value that is assigned to the variable.

These are some of the differences between SET and SELECT statements and as can be seen, it is advisable to use SET when assigning value to a variable but when using queries that are likely to return 0 or more than 1 values, using SELECT would be the correct approach.