Some differences between using set and select values in SQL Server

SET instead of SELECT is recommended for SQL Server.
When the expression returns a value and assigns a value to a variable, the SET method is recommended.

The following table lists the differences between SET and SELECT. Pay special attention to the red part.

The code is as follows:

Copy code

******************/ Drop table # Create table # (OrderId INT) INSERT # (OrderId) VALUES (1) INSERT # (OrderId) VALUES (2) ******************/ ----- Role 1: DECLARE @ id INT SET @ id = 100 SET @ id = (select isnull (OrderId, 0) FROM # WHERE OrderId = 1000000000) SELECT @ id -- The result is NULL. Because there is no order number with OrderId 1000000000, it is NULL. ----- Role 2: DECLARE @ id INT SET @ id = 100 SELECT @ id = ISNULL (OrderId, 0) FROM # WHERE OrderId = 1000000000. SELECT @ id -- The result is 100. Because there is no order number with OrderId 1000000000, the SELECT @ id =... operation fails to assign a value to @ id. ----- Role 3: DECLARE @ id INT SET @ id = 100 SELECT @ id = OrderId FROM # -- order by OrderId DESC SELECT @ id -- If the result is 2, run SELECT @ id =... to return the last value of the returned result to @ id.

Instance

To illustrate the differences, create a data table and insert several data entries.

The code is as follows:	Copy code
Create table tblStudent ( Id nvarchar (10) primary key, Name nvarchar (21) not null, ) Insert into tblStudent (id, name) values ('201312', 'zhangsan '); Insert into tblStudent (id, name) values ('201312', 'Lily '); Insert into tblStudent (id, name) values ('201312', 'Lily ');

1. select can assign values to multiple variables at the same time. set can assign values to only one variable.

Case 1:

The code is as follows:	Copy code
Declare @ variable1 int, @ variable2 int Select @ variable2 = 1, @ variable2 = 2 Set @ variable1 = 1, @ variable2 = 2 error Msg 102, Level 15, State 1, Line 4 Incorrect syntax near ','.

Case 2:

Declare @ variable1 int, @ variable2 int
Set @ variable2 = 1
Set @ variable2 = 2
Select @ variable1 = 1, @ variable2 = 2 passed normally

Command (s) completed successfully

 

.

2. When the returned value is multiple rows, select will not make an error and set will report an error.

 

The code is as follows:

Copy code

Declare @ id nvarchar (10) Print 'set multiple return values :' Set @ id = (select id from tblStudent1 where name = 'Li si ') Print 'set value: '+ @ id -- because there are two persons named Li Si, an error is returned when you assign values using set. Print 'set multiple return values :' Select @ id = id from tblStudent1 where name = 'Li si' Print 'set: '+ @ id -- if you use select to assign values, no error is returned.

Subquery returned more than 1 value in the error code. (that is, the Subquery returns more than one value)

3. If no value is returned, set does not assign a value to the variable. select assigns a value to NULL.

The code is as follows:

Copy code

-- Use set to assign null return values DECLARE @ id varchar (10) SET @ id = 'value' SET @ id = ( SELECT id FROM dbo. tblStudent WHERE id = '**********' ) -- When the set value is used, if no return value is returned for the query, the value of the variable is NULL. SELECT @ id as setResult GO -- Return value with select NULL DECLARE @ id varchar (10) SET @ id = 'value' SELECT @ id = name FROM dbo. tblStudent WHERE id = '**********' -- When a select value is used, if no return value is returned for a query, the variable is not assigned a value. The variable retains the previous value, that is, 'value' SELECT @ id as selectResult GO