The same and different points of SELECT and SET assignment in SQL SERVER

SELECT and SET can be used to assign values to variables in SQL SERVER, but their usage and effect are different in some details.

1. In terms of variable assignment, SET is the ANSI standard assignment method, while SELECT is not. This is one of the reasons why SET is recommended.

2. SELECT can assign multiple variables at one time, while SET can assign only one variable at a time.

DECLARE @NAME NVARCHAR(128), @AGE INT;
SET @NAME = N'Xiao Ming';
SET @AGE=18;
PRINT @NAME;
PRINT @AGE;
GO
DECLARE @NAME NVARCHAR(128), @AGE INT; SELECT @NAME = N'Xiao Ming',@AGE=18; PRINT @NAME; PRINT @AGE;

3. When subquery is used to assign values to variables, the subquery must be a scalar subquery (that is, the result of subquery is a data or a row and a column). Multiple values cannot be returned, otherwise an error will be reported.

1) however, it should be noted that if you assign a value to a variable in a SELECT query statement, the number of records returned by the query statement will not generate an error, and the value of the variable is the corresponding value of the record in the last row of the query statement.

2) if the query result does not return a record, that is to say, when the return value is NULL, SET and SELECT will be SET to NULL, while when the value is assigned in the SELECT query statement, the variable will remain the initial value.

IF (OBJECT_ID('tempdb..#temp') is not null)
BEGIN
    DROP TABLE #temp;
END
ELSE
BEGIN
    CREATE TABLE #temp(
    [Name] NVARCHAR(128) ,
    AGE INT
    )
END
GO
INSERT INTO #temp([Name],AGE) VALUES(N'Xiao Ming',18)
INSERT INTO #temp([Name],AGE) VALUES(N'Xiao Zhang',19)
INSERT INTO #temp([Name],AGE) VALUES(N'Xiao Wang',17)
GO
DECLARE @NAME1 NVARCHAR(128), @AGE1 INT,@NAME2 NVARCHAR(128), @AGE2 INT;
SET @NAME1=(SELECT TOP 1 [NAME] FROM #temp);    --SET Scalar assignment in query
SELECT @AGE1=(SELECT TOP 1 AGE FROM #temp);        --SELECT Scalar assignment in query
SELECT @NAME2=[NAME],@AGE2=[AGE] FROM #temp;    --SELECT Assignment in query statement
PRINT @NAME1; --Proper operation,Display result: Xiaoming
PRINT @AGE1;  --Proper operation,Display result: 18
PRINT @NAME2; --Proper operation,Display result: Xiao Wang
PRINT @AGE2;  --Proper operation,Display result: 17

GO

DECLARE @NAME1 NVARCHAR(128), @AGE1 INT,@NAME2 NVARCHAR(128), @AGE2 INT;
SELECT @NAME1=N'Initial name',@AGE1=0,@NAME2=N'Initial name',@AGE2=0; --Initialize variable values
SET @NAME1=(SELECT TOP 1 [NAME] FROM #temp WHERE 1>1);    --SET Scalar assignment in query
SELECT @AGE1=(SELECT TOP 1 AGE FROM #temp WHERE 1>1);        --SELECT Scalar assignment in query
SELECT @NAME2=[NAME],@AGE2=[AGE] FROM #temp WHERE 1>1;    --SELECT Assignment in query statement
PRINT @NAME1; --Proper operation,Actual value: NULL, Display result: (blank)
PRINT @AGE1;  --Proper operation,Actual value: NULL, Display result: (blank)
PRINT @NAME2; --Proper operation,Actual and display values: initial name
PRINT @AGE2;  --Proper operation,Actual and display values: 0
GO

 

How can we choose which method to use

1. Because SET is the standard of ANSI, it is recommended.

2. In the case of not considering the standard, if the assignment of multiple variables is involved, in order to write less code or obtain the value of multiple global variables, please consider using SELECT. First, it is simple, and second, some global variables will change in the value when the second sentence is executed.

Tags: SQL Sever SQL less

Posted on Mon, 02 Dec 2019 00:12:37 -0500 by Daggeth