asked
sql syntax issues - declaring variables in loops
DECLARE @i INT = 1 WHILE @i < 5 BEGIN DECLARE @j AS TABLE ( i INT ) SET @i = @i + 1 INSERT INTO @j SELECT @i END SELECT * FROM @j on executing the above code it will return rows having values as 2,3,4,5 My question each time loop get executed why it is not giving the error of table already exists
This site runs by you casting votes. For all helpful answers below, click on the thumbs up next to those answers. If any one answer lead to a solution, click on the check mark next to that answer.
You're creating a table variable and not a table. The table var exists during execution. There is some good content from Scott Allen [here][1] [1]:
http://odetocode.com/articles/365.aspx
That doesn't answer why you both don't get a syntax error and the data is retained between each declaration of the variable. I would have thought it would reset it and the final result would be a single row, 5.
The DECLARE doesn't get 'executed' multiple times. In fact the DECLARE isn't 'executed' at all - it's there to define the variables at compile time - the code that gets executed isn't exactly what you've typed. See these discussions on SO : -
http://stackoverflow.com/questions/6045952/variables-scope-which-are-defined-within-a-while-block-in-stored-procedures-sq -
http://stackoverflow.com/questions/14963742/sql-server-variable-loop-vs-duplicate
16 People are following this question.
