T-SQL变量操作详解

此处只讨论普通的标量值变量。不讨论表变量和游标变量。

一个变量必然对应一种特定的（标量）数据类型，变量的状态可能是一个相应数据类型的特定的值，也可能是NULL。因此，想要准确灵活地使用变量，需要对数据类型和NULL相关的三值逻辑有充分理解，参看该文章

以下为T-SQL中变量相关操作：

1. --C语言中变量未赋值时是不确定值，T-SQL中变量未赋值时是NULL 
2. DECLARE @i int, @s varchar(100), @d datetime 
3. SELECT @i, @s, @d 
4. GO 
5. --严谨起见，使用变量时最好赋初值，特别是依赖与变量初值的逻辑 
6. DECLARE @i int, @s varchar(100) 
7. SET @s = NULL --这句可省略，但假如未来版本中变量未赋值时不再是NULL，代码会出错。 
8. SET @i = 0 
9. WHILE @i < 5 
10. BEGIN 
11.     SET @s = ISNULL(@s + ',', '') + CAST(@i AS varchar(10)) 
12.     SET @i = @i + 1 
13. END 
14. SELECT @s 
15. GO 
16.  
17. --根据查询为变量赋值 
18. CREATE TABLE #Nums( 
19.     n int NOT NULL PRIMARY KEY CLUSTERED, 
20.     n2 AS n * n, 
21.     s AS CAST(n AS varchar(10)) + '^2 = ' + CAST(n * n AS varchar(10)) 
22. ) 
23. INSERT INTO #Nums(n) 
24. SELECT TOP(10) n = ROW_NUMBER() OVER(ORDER BY (SELECT 1)) 
25. FROM sys.columns 
26. GO 
27. --方法1：SET 变量 = 标量值（或标量子查询） 
28. DECLARE @i1 int, @i2 int 
29. SET @i1 = (SELECT n FROM #Nums WHERE n = 1) 
30. SET @i2 = (SELECT MAX(n2) FROM #Nums) 
31. SELECT @i1, @i2 
32. GO 
33. --方法2：SELECT 变量 = 标量值（或列名）[ FROM ...] 
34. DECLARE @i1 int, @i2 int 
35. SELECT @i1 = n FROM #Nums WHERE n = 1 
36. SELECT @i2 = MAX(n2) FROM #Nums 
37. SELECT @i1, @i2 
38. GO 
39. --方法1与方法2的不同 
40. --a. 方法2可以一次为多个变量赋值 
41. DECLARE @i11 int, @i12 int, @i13 int, @i14 int 
42. DECLARE @i21 int, @i22 int, @i23 int, @i24 int 
43. SET @i11 = 3 
44. SET @i12 = 9 
45. SET @i13 = (SELECT n FROM #Nums WHERE n = 3) --查询表两次 
46. SET @i14 = (SELECT n2 FROM #Nums WHERE n = 3) 
47. SELECT @i11, @i12, @i13, @i14 
48. SELECT @i21 = 3, @i22 = 9 
49. SELECT @i23 = n, @i24 = n2 FROM #Nums WHERE n = 3 --查询表一次 
50. SELECT @i21, @i22, @i23, @i24 
51. GO 
52. --b. 当查询返回多行结果时，方法1是报错，方法2是多次赋值（保持最后一个值） 
53. DECLARE @i1 int 
54. SET @i1 = (SELECT n FROM #Nums) --报错 
55. GO 
56. DECLARE @i2 int 
57. SELECT @i2 = n FROM #Nums --多次赋值并保持最后一个值 
58. SELECT @i2 
59. /* 
60. 所谓“最后一个值”依赖于查询返回结果集的顺序。 
61. 在不指定ORDER BY的情况下，查询返回顺序依赖于表中数据的物理存储顺序和具体的执行计划，顺序是不确定的。 
62. */ 
63. GO 
64. --c. 当查询返回空结果集（不是NULL）时，方法1是把空结果集转换为NULL（标量）再赋值，方法2是不进行赋值操作 
65. DECLARE @i1 int, @i2 int 
66. SET @i1 = 11111 --初始值 
67. SET @i2 = 22222 --初始值 
68. SET @i1 = (SELECT n FROM #Nums WHERE 1 = 0) 
69. SELECT @i2 = n FROM #Nums WHERE 1 = 0 
70. SELECT @i1, @i2 
71. GO 
72. --因为目前SELECT赋值的本质是循环赋值，可以用以下方法拼接值： 
73. DECLARE @s varchar(100) 
74. SET @s = NULL 
75. SELECT @s = ISNULL(@s + ',', '') + CAST(n2 AS varchar(10)) 
76. FROM #Nums 
77. ORDER BY n 
78. SELECT @s 
79. --但微软官方不保证这种SELECT赋值机制未来不会改变。 
80. GO 
81. --在SQL Server 2005之后可以用以下方法取代： 
82. DECLARE @s varchar(100) 
83. SET @s = STUFF((SELECT ',' + CAST(n2 AS varchar(10)) FROM #Nums ORDER BY n FOR XML PATH('')),1,1,'') 
84. SELECT @s 
85. GO 
86.  
87. --T-SQL变量作用域：在当前会话中，从变量声明处到当前批的结束。 
88. --与代码块无关 
89. DECLARE @outer int 
90. SET @outer = 1 
91. BEGIN 
92.     DECLARE @inner int 
93.     SET @inner = 2 
94.     SELECT @outer, @inner 
95. END 
96. SELECT @outer, @inner 
97. --只能通过参数传递到子会话 
98. EXEC sp_executesql N'SELECT @vo, @vi', N'@vo int, @vi int', 
99.     @vo = @outer, @vi = @inner  --调用存储过程和函数也是一样 
100. PRINT '====到此为止是OK的====' 
101. --当前会话的变量作用域不能延伸到子会话 
102. EXEC('SELECT @outer, @inner')   --调用存储过程和函数也是一样 
103. GO 
104. --不能跨批 
105. SELECT @outer, @inner 
106. GO