In SQL Server, there are two ways to assign a value to a variable: SET and SELECT. Both methods have their own use cases and it’s important to understand how they work.

Using SET to Assign a Value to a Variable

SET is a simple and straightforward way to assign a value to a variable. Here’s an example:

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

The result of this code will be 10. The value 10 is assigned to the variable @no using the SET keyword.

Using SELECT to Assign a Value to a Variable

SELECT can also be used to assign a value to a variable. Here’s an example:

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

The result of this code will also be 10. The value 10 is assigned to the variable @no using the SELECT statement.

Assigning Values from a Table to a Variable

You can also assign values from a table to a variable. Let’s say we have a table called #products with columns prod_id and prod_name:

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'

If 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

The result of this code will be 10003. The value of prod_id for the product “Micromax” is assigned to the variable @prod_id using the SET keyword.

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

The result of this code will also be 10003. The value of prod_id for the product “Micromax” is assigned to the variable @prod_id using the SELECT statement.

Limitations of SET and SELECT for Variable Assignment

It’s important to note that SET and SELECT have different behaviors when it comes to assigning values from a query that returns multiple rows.

If a query returns multiple values and you try to assign them to a single variable using SET, you will get an error. Here’s an example:

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

This code will result in an error because the query returns multiple values that cannot be assigned to a single variable using SET.

However, if you use SELECT instead, you will not get an error. The last available value will be assigned to the variable. Here’s an example:

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

The result of this code will be 10005. The last available value from the query is assigned to the variable @prod_id.

Therefore, it’s important to keep in mind the behavior of SET and SELECT when assigning values to variables in SQL Server.