0 votes
1 view
in SQL by (20.3k points)

What are the differences between the SET and SELECT statements when assigning variables in T-SQL?

1 Answer

0 votes
by (40.3k points)
edited by

Differences between SET and SELECT while assigning the variables are as follows:

Want to learn SQL from scratch? Here's is the right video for you on SQL provided by Intellipaat

SET

SELECT

It’s ANSI standard for variable assignment.

Whereas, SELECT is Non-ANSI standard.

It can assign only one variable at a time.

Whereas, this can assign values to more than one variable at a time.

Set assigns a null value to the variables, when you assign from the query and the query returns no result.

Example:

DECLARE @CustomerID NCHAR(5)

SET @CustomerID = 'XYZ'

SET @CustomerID = (SELECT [CustomerID]

                   FROM [dbo].[Customers]

                   WHERE [CustomerID] = 'ABC')

SELECT @CustomerID -– Returns NULL

When you assign from a query and the query returns no result. SELECT doesn’t make the assignment and hence it can’t change the value of the variable

Example:

DECLARE @CustomerID NCHAR(5)

SET @CustomerID = 'XYZ'

SELECT @CustomerID = [CustomerID]

FROM [dbo].[Customers]

WHERE [CustomerID] = 'ABC'

SELECT @CustomerID –- Returns XYZ

When you assign from the query that returns more than one value, then SET results into an error

Example:

SET = (SELECT [CustomerID]

       FROM [dbo].[Customers])

 

Msg 512, Level 16, State 1, Line 3

Subquery returned more than 1 value.

This is not permitted when the subquery

follows =, !=, <, <= , >, >= or when the

subquery is used as an expression.

When you assign from the query that returns more than one value, SELECT assigns the last value returned by the query and hides the fact that the query returned more than one row.

Example:

SELECT @CustomerID = [CustomerID]

FROM [dbo].[Customers]

-- No error generated

Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...