October 16, 2007 at 8:08 am
I have some code I am writing and just wondering if this will cause some issues i.e. memory leaks etc. here it is
declare @v1 int
set @v1 = 0
while @v1 < 10
begin
declare @p int
set @p = 5
set @v1 = @v1 + 1
end
I am wondering what actually takes place inside of SQL Server when the variable @p is redeclared in the loop. Does the previous @p variables get released? Not sure -- I know I can declare this variable outside of the loop but I would like to understand more clearly what happens.
October 17, 2007 at 7:19 am
My guess is that it won't be declared more than once, after the first time it's decalred, only the assignment will happen.
I'm guessing this due to the fact you can't explicitly declare the same variable more than once within the same batch.
Pure speculation, though
/Kenneth
October 17, 2007 at 9:44 am
Hi Kenneth,
Yes this is correct but if you were to write some code like this
declare @p
declare @p
and then run it an error like this would be generated when parsing the query:
The variable name '@p' has already been declared. Variable names must be unique within a query batch or stored procedure.
This supports your answer but when I parse my original loop query there is no error. Any idea for this -- SQL Server intelligent enough to ignore the future declarations in the iterations of the loop?
The more I write I am thinking as a rule declarations of variables in a conditional loop are a bad idea -- Your thoughts.
Thanks,
Mark
October 18, 2007 at 1:57 am
Well, haven't given it much thought actually.
I routinely never declare/create/drop stuff inside loops unless restricted by scope or forced to do it for some reason. For the 'normal' stuff, I always declare and init everything in the beginning. (outside of loops and such)
/Kenneth
October 18, 2007 at 2:38 am
I wonder - which programming language will let you declare a variable in loop?
_____________
Code for TallyGenerator
October 18, 2007 at 3:00 am
T-SQL does. 😀
October 18, 2007 at 3:02 am
October 18, 2007 at 3:19 am
😉
Look at the top.
The declaration of @p is inside the while loop.
...unless you're really asking for something else? 😎
October 18, 2007 at 3:55 am
Kenneth Wilhelmsson (10/18/2007)
😉Look at the top.
The declaration of @p is inside the while loop.
...unless you're really asking for something else? 😎
No.
It's text editor what allows to put declarations of variables inside of loops.
If T-SQL would do it there would not be this topic.
😛
_____________
Code for TallyGenerator
October 18, 2007 at 4:37 am
Kenneth seems to be right about the declaration being evaluated only once, and then only assignments happening. If you add a print statement to the original code and set @p to be @v1 like:
declare @v1 int
set @v1 = 0
while @v1 < 10
begin
declare @p int
print @p
set @p = @v1
set @v1 = @v1 + 1
end
In the first iteration p is not set, so print results in an empty line. Subsequently @p is no longer null, even though you redeclare it, and it keeps the previous value.
So the result will be
------
0
1
2
3
4
5
6
7
8
------
It is indeed weird 🙂
Regards,
Andras
October 18, 2007 at 5:22 am
Here is one trick which can help to understand it.
There is one variable which you can see declared.
It's table variable.
[font="Courier New"]----------------------------------
SELECT * FROM tempdb..sysobjects WHERE name LIKE '#%'
GO
SELECT * FROM tempdb..sysobjects
SELECT 'Not declared yet'
DECLARE @Table TABLE (ID int)
SELECT * FROM tempdb..sysobjects
SELECT 'Already declared'
GO
-----------------------------------[/font]
As you can see memory is reserved for declared variable when script is being compiled, before its execution.
All declarations happen during compilation, all DECLARE statements are ignored on execution time, that's why it does not matter where you put it, declaration happens once per batch.
_____________
Code for TallyGenerator
October 18, 2007 at 5:34 am
Nice, so then my guess was right on the nail 🙂
...and the anser to the op is; No, there is no risk of memoryleaks or 'runaway' multiples of overwritten variables etc, since the declaration only happens once.
October 18, 2007 at 5:45 am
Kenneth Wilhelmsson (10/18/2007)No, there is no risk of memoryleaks or 'runaway' multiples of overwritten variables etc, since the declaration only happens once.
There is only risk of fooling yourself.
_____________
Code for TallyGenerator
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply