0

Why DECLARE statement is making declared variables to be global?

For example:

SET NOCOUNT ON
GO

    DECLARE @LoopCounter TINYINT = 3

    WHILE @LoopCounter <> 0
    BEGIN

        DECLARE @TempDataSource TABLE ([ID] TINYINT)

        SELECT [ID]
        FROM @TempDataSource

        INSERT INTO @TempDataSource ([ID])
        VALUES (1)
              ,(2)
              ,(3)

        SET @LoopCounter = @LoopCounter - 1
    END

SET NOCOUNT OFF
GO

Gives me:

enter image description here

Should it returns nothing in all cases?

gotqn
  • 42,737
  • 46
  • 157
  • 243
  • 1
    The scope of `@TempDataSource` is everything between the `GO`s. It will not be re-declared with every iteration. – Khan Oct 22 '13 at 14:05
  • 8
    SQL Server does not have global/local variables. `The scope of a variable is the range of Transact-SQL statements that can reference the variable. The scope of a variable lasts from the point it is declared until the end of the batch or stored procedure in which it is declared.` – Martin Smith Oct 22 '13 at 14:07
  • @MartinSmith But, is it natural if a DECLARE statement is executing several times, what was recorded in the variable to be lost, or maybe the Server is executing the DECLARE statement one time only? – gotqn Oct 22 '13 at 14:14
  • 9
    The `DECLARE` isn't really executed at all as you seem to expect. Try `SELECT * FROM tempdb.sys.columns WHERE name = 'gotqn';DECLARE @T TABLE(gotqn INT)`. You see the table variable is actually created already before the `DECLARE` is even encountered. – Martin Smith Oct 22 '13 at 14:17
  • @MartinSmith Thanks for the references and comments. This make sense. – gotqn Oct 22 '13 at 14:21
  • 1
    Related: [How do I drop table variables in SQL-Server? Should I even do this?](http://stackoverflow.com/questions/5653520/how-do-i-drop-table-variables-in-sql-server-should-i-even-do-this) – Andriy M Oct 22 '13 at 14:42
  • @MartinSmith I just wanted to add that the discussion above is not valid for temp tables. SELECT * FROM tempdb.sys.columns WHERE name = 'gotqn'; CREATE TABLE #T(gotqn INT) – gotqn Nov 06 '13 at 08:02

0 Answers0