Detailed description of T-SQL variable operation

Only common scalar variables are discussed here. Table variables and cursor variables are not discussed.
A variable must correspond to a specific (scalar) data type. The state of the variable may be a specific value of the corresponding data type, or it may be null. Therefore, to use variables accurately and flexiblyData TypeNull-relatedThree-value LogicFor more information, see this post:
Http://topic.csdn.net/u/20100826/18/7b81012a-b5c4-48b1-b5d1-40a92f3e0388.html
The following are variable-related operations in the T-SQL:

SQL code

-- The C language variable is not assigned a value when the value is not sure, the T-SQL variable is not assigned a value when nulldeclare @ I int, @ s varchar (100), @ d datetimeselect @ I, @ s, @ DGO -- for the sake of rigor, it is best to assign initial values when using variables, especially the logic declare @ I int, @ s varchar (100) of dependency and initial values of variables) set @ s = NULL -- this sentence can be omitted, but it is no longer null if the variable is not assigned a value in future versions, Code An error occurs. Set @ I = 0 while @ I <5 begin set @ s = isnull (@ s + ',', '') + Cast (@ I as varchar (10 )) set @ I = @ I + 1 endselect @ sgo -- assign the CREATE TABLE # Nums (N int not null primary key clustered, N2 as N * n, s as cast (N as varchar (10) + '^ 2 =' + Cast (N * n as varchar (10) insert into # Nums (N) select top (10) n = row_number () over (order by (select 1) from sys. columnsgo -- Method 1: Set variable = scalar value (or scalar query) Declare @ I1 int ,@ I2 intset @ I1 = (select N from # Nums where n = 1) set @ I2 = (select max (N2) from # Nums) Select @ I1, @ i2go -- Method 2: select variable = scalar value (or column name) [from...] declare @ I1 int, @ I2 intselect @ I1 = N from # Nums where n = 1 select @ I2 = max (N2) from # numsselect @ I1, @ i2go -- difference between method 1 and method 2 --. method 2 can assign values to multiple variables at a time: declare @ i11 int, @ i12 int, @ I13 int, @ i14 intdeclare @ i21 int, @ i22 int, @ i23 int, @ I24 intset @ i11 = 3 set @ i12 = 9 set @ I13 = (select N from # Nums where n = 3) -- query the table twice set @ i14 = (select N2 from # Nums where n = 3) Select @ i11, @ i12, @ I13, @ i14select @ i21 = 3, @ i22 = 9 select @ i23 = N, @ I24 = n2 from # Nums where n = 3 -- query a table once select @ i21, @ i22, @ i23, @ i24go -- B. when a query returns multiple rows of results, method 1 returns an error, and method 2 returns multiple values (keep the last value). Declare @ I1 intset @ I1 = (select N from # Nums) -- error godeclare @ I2 intselect @ I2 = N from # Nums -- assign values multiple times and keep the last value select @ I2/* the so-called "Last A value depends on the order in which the returned result set is queried. If order by is not specified, the query return sequence depends on the physical storage sequence of the table data and the specific execution plan. The order is uncertain. */Go -- C. when a query returns an empty result set (not null), method 1 converts the empty result set to null (scalar) and then assigns a value. method 2 does not assign a value to declare @ I1 Int, @ I2 intset @ I1 = 11111 -- Initial Value set @ I2 = 22222 -- Initial Value set @ I1 = (select N from # Nums where 1 = 0) select @ I2 = N from # Nums where 1 = 0 select @ I1, @ i2go -- because the current nature of select assignment is cyclic assignment, you can splice values using the following methods: declare @ s varchar (100) set @ s = nullselect @ s = isnull (@ s + ',', '') + Cast (N2 as varchar (10 )) from # numsorder by nselect @ s -- but Microsoft does not guarantee such a select The assignment mechanism will not change in the future. Go -- replace declare @ s varchar (2005) set @ s = stuff (select ', '+ Cast (N2 as varchar (10) from # Nums order by N for XML Path (''),'') Select @ sgo -- T-SQL variable scope: in the current session, from the variable declaration to the end of the current batch. -- It has nothing to do with the code block. Declare @ outer intset @ outer = 1 begin declare @ inner int set @ inner = 2 select @ outer, @ innerendselect @ outer, @ inner -- only parameters can be passed to the subsession exec sp_executesql n'select @ Vo, @ vi', n' @ VO int, @ VI int', @ Vo = @ outer, @ Vi = @ inner -- the same is true for calling stored procedures and functions. Print '==== so far is OK ====' -- the variable scope of the current session cannot be extended to sub-sessions. session exec ('select @ outer, @ inner ') -- the same is true for calling stored procedures and functions. You cannot select @ outer, @ innergo across batches.
Http://topic.csdn.net/u/20101223/10/FFCC1E6F-92CE-4479-BF07-1DE46BF2A9BF.html