SET Vs SELECT When Assigning Values to Variables in SQL Server

SET

A “SET” expression sets the specified local variable created previously to the given value.

Syntax

SET @localvariable = value

Here @localvariable is a local variable with any data type or cursor and value is any expression valid in SQL Server. After declaration, all variables are initialized with a NULL value. Using a SET statement we can assign a value to those variables. We must use a separate SET statement of each variable when there are multiple variables.

Examples

–Simple example

DECLARE @myTest VARCHAR(10)

SET @myTest = ‘Jignesh’

 

–Expression example

DECLARE @a INT = 20;

DECLARE @b INT = 40;

DECLARE @c INT ;

 

SET @c = @a + @b;
SELECT

“SELECT” is designed to return row data. The main purpose of the “SELECT” statement is to retrieve a row from the database and allow the selection of one or more rows and columns from one or more database tables. We can also use a “SELECT” statement to assign a value to a local variable.

Syntax

SELECT @localvariable = value

Examples

DECLARE @myTest VARCHAR(10)

SELECT @myTest = ‘Jignesh’

 

–Multiple assignment example

DECLARE @A VARCHAR(10)

DECLARE @B VARCHAR(10)

SELECT @A=’Jignesh’, @B=’Tejas’

 

SET SELECT
SET is ANSI standard for assigning a value to a local variable. SELECT is not an ANSI standard for assigning values to variables.
We can assign a value to one variable at a time.
Example

DECLARE @a INT

DECLARE @b INT

DECLARE @c INT

 

SET @a = 1;

SET @b = 2;

SET @c = 3;

We can assign a value to one or more variables at a time.
Example

DECLARE @a INT

DECLARE @b INT

DECLARE @c INT

 

SELECT @a = 5, @b = 6, @c = 7

 

When  assigning a level from a query to a variable and if the query does not return any results then the “SET” statement assigns a NULL value to the local variable.

Example

DECLARE @a VARCHAR(10)= ‘jignesh’

SET @a = (SELECT CustomerCode FROM CustomerMasterWHERE 1 !=1)
PRINT @a

When a value is assigne from the query to the variable and if the query does not return any results then the “SELECT” statement does not change the value of the local variable.

Example

DECLARE @b varchar(10)= ‘jignesh’

SELECT @b = CustomerCodeFROMCustomerMasterWHERE 1 !=1

PRINT @b

When a value is assigned from a query to a variable and if the query returns more than value then the “SET” statement raises an error.

DECLARE @a VARCHAR(10)= ‘jignesh’

SET @a = (SELECT CustomerCode FROM CustomerMaster )

–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. When value assigning from the query to the variable and if query returns more than one value than “SELECT” statement will assign the last value of query result to the local variable. It does not raise any error.

When a value is assigned from a query to a variable and if the query returns more than value then the “SELECT” statement will assign the last value of the query results to the local variable. It does not raise an error.
SELECT is slightly faster than SET because SELECT is able to assign more than one variable at once whereas SET can assign one variable at once.

Choose between SET and SELECT

“SET” will accept and assign a scalar value from the query while SELECT can accept multiple values from the query. But there is no way to determine which value is present in the variable after accepting multiple values using a SELELCT expression. With a SELECT expression, the last value populates from the returned list. Here the situation becomes unexpected because there is no warning and error whereas SET raises an error in this situation.

Use SELECT instead of SET when you need to populate multiple variables at once. SELECT requires a single statement to populate multiple variables whereas SET requires multiple statements.

The behavior of SET and SELECT expressions will be different when they are used with a query returning a null result. A SELECT expression would preserve the previous value of variable while the “SET” expression would set a null value in the above situation.

A SELECT expression may be a good choice in some scenarios, but SELECT is not ANSI standard. If you are strictly following standards then use SET instead of SELECT.

Summary

Depending on the scenario we may use either a SET or SELELCT expression.

My preference is to use a SELELCT expression in the following scenario:

  • Multiple variables are assigned a value from the query or table or assigned a value directly.
  • Less code for assigning a value to multiple variables.

We may use a SET expression in the following scenario:

  • If we want to assign a single value to a local variable
  • Need to follow ANSI Standards
  • NULL assignment to the variable is expected when null is returned in the result set
  • Exceptions can be accepted when multiple rows are returned from the result set.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s