January 5, 2010 at 1:33 am
Whenever i am declaring a variable inside a loop , why sql server is creating a new instance of variable
when i say declare @a int. Why ???
declare @i int
set @i = 1
while @i< = 5
begin
declare @a char(10)
PRINT 'Before ... ' + isnull(@a,'**unknown**')
set @a = @i
PRINT 'After ... ' +@a
set @i = @i + 1
end
/*
Before ... **unknown*
After ... 1
Before ... 1
After ... 2
Before ... 2
After ... 3
Before ... 3
After ... 4
Before ... 4
After ... 5
*/
Expecting the output as below
Before ... **unknown*
After ... 1
Before ... **unknown*
After ... 2
Before ... **unknown*
After ... 3
Before ... **unknown*
After ... 4
Before ... **unknown*
After ... 5
January 5, 2010 at 2:12 am
Personally I have to admit that I’m surprised that you don’t get a run time error saying that you are declaring a variable that was already declared. I guess that the variables declaration is being processed during compilation time and during run time it is being ignored. Can you explain what are you trying to do? If you want to initialize the variable inside the loop to null, you can simply make it equal null and you don’t have to declare it again. If you are declaring it inside a loop for another reason, can you explain the reason for that? Most chances are that someone will find a better way to do what you are trying to do.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 5, 2010 at 2:15 am
SQLServer doesnt destroy the variable when it drops out of scope, for instance this works
declare @l integer
select @l = 1
while(@l<10) begin
declare @x integer
select @x = @l
select @l+=1
end
select @x
Also its worth pointing out that table variables are not truncated / cleaned either.
January 5, 2010 at 4:09 am
Dave Ballantyne (1/5/2010)
SQLServer doesnt destroy the variable when it drops out of scope, for instance this works
declare @l integerselect @l = 1
while(@l<10) begin
declare @x integer
select @x = @l
select @l+=1
end
select @x
Also its worth pointing out that table variables are not truncated / cleaned either.
How do you define drop out of scope? My definition is leaving the code that the variable was declared in because it finished running (either because it got to a return statement or because it got to the end of the written code or because of a runtime error). If your definition is the same as mine, then I have to disagree with you because SQL Server frees the memory that was used by variables of the code that finished running (accept for cursors). The example that you supplied the variable was declared and used in the same scope.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 5, 2010 at 5:32 am
Adi Cohn-120898 (1/5/2010)
How do you define drop out of scope? My definition is leaving the code that the variable was declared in because it finished running (either because it got to a return statement or because it got to the end of the written code or because of a runtime error). If your definition is the same as mine, then I have to disagree with you because SQL Server frees the memory that was used by variables of the code that finished running (accept for cursors). The example that you supplied the variable was declared and used in the same scope.
Adi
"Scope" in the context of this question, to my mind, is what would be the scope in a language such as C/C++ or VB. So when you hit an 'end block' ie curly brace 'End If' , 'Next' then all variables / classes / whatevers defined within are destroyed/dealloced and the next loop around will be using fresh variables and the values will not be kept.
Within the SQL Language i agree with you 100%.
January 5, 2010 at 11:45 pm
Hi Adi,
I have observed while one the developer came to me that why the table variable is not getting
deallocated when it is declared inside a WHILE loop. at any moment the the SELECT which is inserting
data into the table variable will insert only one record. but we can observe the prvious reords
are being maintained in the table. For that, we tried with TRUNATE but did'nt work.
Later we have included the DELETE stmt within the WHILE loop to flush the table.
--look at this eventhough we are accessing the variable @x it is out of scope , why it
-- is not throwing an error.
-- Is this a bug in SQL Server ? The same with the case in SQL 2000 and 2005.
declare @l integer
select @l = 1
while(@l<10) begin
declare @x integer
select @x = @l
select @l=@l+1
end
select @x
--output
9
January 8, 2010 at 11:48 am
Came to know that the scope a variable is a batch not the loop or anything else.
Hope this helps!
January 10, 2010 at 3:15 pm
DECLARE statement is not executeable at run time.
It just reserves memory needed for variables (including table variables).
Memory allocation happens when a script is being parsed and compiled.
That's why no matter where you put DECLARE it will be executed only once - when compiler is reading the statement.
Loops are executed on run time. At that time the script is already compiled and memory is allocated.
DECLARE is not a part of run-time routine and is not included into compiled code.
Hope it helps.
_____________
Code for TallyGenerator
January 15, 2010 at 7:40 pm
Thank You.:-)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply