在存储过程的while块中定义的变量范围-SQl服务器 [英] Variables scope which are defined within a while block in stored procedures - SQl Server
问题描述
在存储过程中，我遇到了一个有趣的场景（至少对我来说）。希望对此有专家的意见和想法。
I've come across a interesting scenario (at least for me) in a stored procedure. Would like to have experts opinion and thoughts on it.
DECLARE @loopcounter INT
SET @loopcounter=10
WHILE @loopcounter > 0
BEGIN
DECLARE @insidevalue int
IF (@loopcounter%2 = 0)
SET @insidevalue = @loopcounter
PRINT 'Value_' + CAST(@insidevalue AS NVARCHAR) + '_'
SET @loopcounter = @loopcounter - 1
END
我期望此块将提供如下输出
I was expecting this block will give the output as below
Value_10_
Value_ _
Value_8_
Value_ _
Value_6_
Value_ _
Value_4_
Value_ _
Value_2_
Value_ _
相反，我得到的输出如下：
Instead I got output as below:
Value_10 _
Value_10_
Value_10 _
Value_10_
Value_8 _
Value_8_
Value_8 _
Value_8_
Value_6 _
Value_6_
Value_6 _
Value_6_
Value_4 _
Value_4_
Value_4 _
Value_4_
Value_2 _
Value_2_
Value_2 _
Value_2_
我想如果我在while块中声明一个变量，那么对于每次迭代，它将值重置为NULL或默认值（来自c＃背景）。
I thought if I declare a variable inside a while block, then for every iteration it will reset the value to NULL or default value (from c# background).
如果这是设计使然，那么我的问题是SQLServer如何在while块中为该变量处理'DECLARE'语句？
If this is by design then my question is how does SQLServer treat 'DECLARE' statement for that variable inside while block? Does it ignore it as the variable is already in memory?
有人可以向我解释这种行为吗？
Can somebody please explain me this behavior?
推荐答案
在这种情况下，变量作用域是整个批处理的存储过程。
The variable scope is the whole batch in this case a stored procedure.
它不会在每个循环中重新声明
It isn't re-declared every loop
所以这完全符合预期
编辑：
最近有一篇博客文章，该文章非常相似。作者很快得到纠正：-）
There is a recent blog article which is quite similar. The author was quickly corrected :-)
这篇关于在存储过程的while块中定义的变量范围-SQl服务器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
