question

Bhupendra99 avatar image
Bhupendra99 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
syntaxvariablewhile
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
@SQLShark avatar image
@SQLShark answered
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
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.