Mighty (7/30/2014)
For me the way how SQL Server handles the declaration of the variables is a bit strange.This piece of code doesn't return an error, even though the declaration of @j-2 doesn't seem to happen.
DECLARE @i INT = 1WHILE @i < 0 -- While loop will not be entered
BEGIN
PRINT 'DECLARE @j-2'
DECLARE @j-2 AS TABLE
(
i INT
)
SET @i = @i + 1
INSERT INTO @j-2
SELECT @i
END
SELECT * FROM @j-2
All variables are declared at compile time. at this time, all control flow commands are ignored.
This is shown, for example, in the following:
DECLARE @i INT = 1
WHILE @i < 5
BEGIN
PRINT 'DECLARE @j-2'
IF ISNUMERIC(@i) = 0
DECLARE @j-2 AS TABLE
(
i CHAR(1)
)
ELSE
DECLARE @j-2 AS TABLE
(
i INT
)
SET @i += 1
INSERT @j-2
VALUES(@i)
END
This fails with the error below, as @j-2 is declared in two places, despite the control flow ensuring it will only be used once.
Msg 134, Level 15, State 1, Line 11
The variable name '@j' has already been declared. Variable names must be unique within a query batch or stored procedure.
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”