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.