Shulou(Shulou.com)05/31 Report--

This article mainly introduces what is the difference between SELECT and SET assignment in SQL SERVER, it has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

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

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

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

DECLARE @ NAME NVARCHAR, @ AGE INT;SET @ NAME = N 'Xiaoming'; SET @ AGE=18;PRINT @ NAME;PRINT @ AGE;GODECLARE @ NAME NVARCHAR, @ AGE INT;SELECT @ NAME = N 'Xiaoming', @ AGE=18;PRINT @ NAME;PRINT @ AGE

3. When using a subquery to assign a value to a variable, the subquery must be a scalar quantum query (that is, the result of the subquery is a data or a row and a column), and cannot return multiple values, otherwise an error will be reported.

1) however, it should be noted that when assigning a value to a variable in a SELECT query statement, the query statement will not cause an error in the number of records returned by the query statement, and the value obtained by the variable is the corresponding value of the record in the last line of the query statement.

2) if the query result does not return a record, that is to say, when the null value is returned, the way the whole subquery is assigned, both SET and SELECT will be set to NULL, while in the SELECT query statement, the variable will remain at its initial value unaffected.

IF (OBJECT_ID ('tempdb..#temp') is not null) BEGIN DROP TABLE # temp ENDELSEBEGIN CREATE TABLE # temp ([Name] NVARCHAR, AGE INT) ENDGOINSERT INTO # temp ([Name], AGE) VALUES (N`Xiaoming', 18) INSERT INTO # temp ([Name], AGE) VALUES (N`Xiao Zhang', 19) INSERT INTO # temp ([Name], AGE) VALUES (N`Xiao Wang, 17) GODECLARE @ NAME1 NVARCHAR, @ AGE1 INT,@NAME2 NVARCHAR (128), @ AGE2 INT;SET @ NAME1= (SELECT TOP 1 [NAME] FROM # temp) -- SET scalar in query assignment SELECT @ AGE1= (SELECT TOP 1 AGE FROM # temp);-- SELECT scalar assignment PRINT @ NAME1; in query assignment SELECT @ NAME2= [name], @ AGE2= [age] FROM # temp;-- SELECT query statement-- correct operation, display result: Xiaoming PRINT @ AGE1;-- correct operation, display result: 18PRINT @ NAME2;-- display result: Wang PRINT @ AGE2 -- run correctly and display the results: 17GODECLARE @ NAME1 NVARCHAR, @ AGE1 INT,@NAME2 NVARCHAR, @ AGE2 INT;SELECT @ NAME1=N' initial name', @ AGE1=0,@NAME2=N' initial name', @ AGE2=0;-- initialize each variable value SET @ NAME1= (SELECT TOP 1 [NAME] FROM # temp WHERE 1 > 1);-- SET scalar in query assignment SELECT @ AGE1= (SELECT TOP 1 AGE FROM # temp WHERE 1 > 1) -- SELECT scalar in query assignment SELECT @ NAME2= [name], @ AGE2= [age] FROM # temp WHERE 1 > 1;-- SELECT query assignment PRINT @ NAME1;-- correct operation, actual value: NULL, display result: (blank) PRINT @ AGE1;-- correct operation, actual value: NULL, display result: (blank) PRINT @ NAME2;-- correct operation, actual and display value: initial name PRINT @ AGE2 -- run correctly, actual and displayed values: 0GO

So how do we choose which way to use:

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

two。 Without considering the standard, if it involves assigning values to multiple variables, in order to write less code or get the values of multiple global variables, consider using SELECT, first, because it is simple, and second, some global variables will change their values when the second sentence is executed.

Thank you for reading this article carefully. I hope the article "what is the difference between SELECT and SET assignment in SQL SERVER" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.