SQL SERVER – An Observation SET vs SELECT in T-SQL

In SQL Server both SET and SELECT can be used to assign a value to a variable as shown in the following example

DECLARE @no INT
SET @no=10
SELECT @no AS no
GO


DECLARE @no INT
SELECT @no=10
SELECT @no AS no
GO

The result is 10

You can also assign valaues taken from a table to a variable.

Let us create the following dataset.

CREATE TABLE #products(prod_id INT, prod_name VARCHAR(100))
INSERT INTO #products(prod_id,prod_name)
SELECT 10001,'Samsung' UNION ALL
SELECT 10002,'Sony' UNION ALL
SELECT 10003,'Micromax' UNION ALL
SELECT 10004,'Lava' UNION ALL
SELECT 10005,'LG'

Suppose you want to know the product id for the product “Micromax” and assign it to a variable. You can do it in two ways

DECLARE @prod_id INT
SET @prod_id =(SELECT prod_id FROM #products WHERE prod_name='Micromax')
SELECT @prod_id AS prod_id
GO

DECLARE @prod_id VARCHAR(100)
SELECT @prod_id = prod_id FROM #products WHERE prod_name='Micromax'
SELECT @prod_id AS prod_id

The result is 10003

Do you think it will always work in the same way? No. Here is an example.

You will get an error when you execute the following code using SET because the query returns mutile values that cannot be assigned to a single variable

DECLARE @prod_id INT
SET @prod_id =(SELECT prod_id FROM #products)
SELECT @prod_id AS prod_id
GO

But when you use SELECT you will not get an error

DECLARE @prod_id VARCHAR(100)
SELECT @prod_id = prod_id FROM #products
SELECT @prod_id AS prod_id

The result is 10005. The lastly available value is assigned to the variable. So you need to keep this in mind when using SET and SELECT.

Reference: Pinal Dave (https://blog.sqlauthority.com)