Hi, this post explores the difference between SET and SELECT Statement in SQL Server and when to use them.
SET
|
SELECT
|
STANDARDS
|
|
SET
is the ANSI standard for assigning values to variables
|
SELECT
is not ANSI Standard.
|
MULTIPLE VALUES
ASSIGNMENT
|
|
SET
allows you to assign data to only one variable at a time
Example:
DECLARE
@varFirstName nvarchar(30),@varLastName nvarchar(30);
SET
@varFirstName = (SELECT FirstName FROM EmployeeMaster where
EmployeeId = 1)
SET
@varLastName = (SELECT LastName FROM
EmployeeMaster where EmployeeId = 1)
PRINT
@varFirstName;--Prints Alpha
PRINT @varLastName;--Prints A
Example2:
SET
@varFirstName = 'Alpha';
SET @
varLastName = A;
|
We
you can use SELECT to assign values to more than one variable at a time.
In
performance SELECT is better.
Example:
DECLARE
@varFirstName nvarchar(30),@varLastName nvarchar(30);
SELECT
@varFirstName = FirstName, @varLastName =
LastName FROM EmployeeMaster where EmployeeId =
1
PRINT
@varFirstName;--Prints Alpha
PRINT @varLastName;--Prints A
Example2:
SELECT
@varFirstName = 'Alpha', @varLastName = 'A'
|
SUB QUERY RETURNING MULTIPLE
VALUES
|
|
SET
Statement throws error when the sub query returns more than one value
Example:
DECLARE
@varFirstName nvarchar(30);
SET
@varFirstName = (SELECT FirstName FROM dbo.EmployeeMaster)
SELECT
@varFirstName--Returns Error
PRINT
@varFirstName--Returns Error
Errror
Returned –
Subquery
returned more than 1 value. This is not permitted when the subquery follows
=, !=, <, <= , >, >= or when the subquery is used as an
expression.
|
SELECT
Statement will not return when sub query returns more than one value
Example:
DECLARE
@varFirstName nvarchar(30)
SELECT
@varFirstName = FirstName FROM
dbo.EmployeeMaster
PRINT
@varFirstName--Returns Juliet
|
NULL VALUE ASSIGNMENT
|
|
SET
Statement will assign Null values to the variable when the result is not
found
Example:
DECLARE
@varFirstName nvarchar(30)
--Assign Dummy values
SET
@varFirstName = 'TEST'
SET
@varFirstName = (SELECT FirstName FROM dbo.EmployeeMaster WHERE EMPLOYEEID =
45 )
PRINT
@varFirstName--Prints NULL
|
SELECT
Statement will not assign Null values to the variable when the result is not
found instead it leaves the value unchanged
Example:
DECLARE
@varFirstName nvarchar(30)
--Assign Dummy values
SET
@varFirstName = 'TEST'
SELECT
@varFirstName = FirstName FROM dbo.EmployeeMaster WHERE EMPLOYEEID =
45
PRINT
@varFirstName--Prints TEST
|
When to use SET statement
Since Set Statements are ANSI Standards, use SET if there is any migration
planned for the database to any other RDBMS
When only one variable is to be assigned.
When we are sure that the sub query returns only one value
NULL assignments are expected (NULL returned in result set)
When NULL Values are to be assigned if the result is not found
Use it assign values to a variable directly
When to use SELECT
statement
When Multiple variables are to be populated by a single query
Since Multiple Variables can be assigned, can be used when we need
to reduce the code
When performance is to be improved
When Null values can be ignored if the result is not found
If we need to get @@ROWCOUNT and @ERROR for last statement executed
Also Check Out -
1) Truncating SQL Server Log files to free space occupied by SQL Server using DBCC SHRINKFILE Command. Resolving Error:9002 in SQL Server
2) Create a New table by copying all rows or column data from an Existing table that is another table
No comments:
Post a Comment