SQL Server - Global Variable Resets in Nested While Loop

Question

I'm having a global Variable, which get increment in the Nested While Loop. In Each Iteration it Resets the Value to 1 (i.e., Initially Initialized Value).

Sample Re-produce able Code:

SET NOCOUNT ON;
DECLARE @GlobalCounter INT = 1;
DECLARE @CounterOne INT = 1;
DECLARE @CounterTwo INT = 1;

WHILE(@CounterOne <= 10)
BEGIN
    PRINT 'Outer Loop:';
    PRINT @GlobalCounter;

    DECLARE @SerialInfo table
    (
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [SNumber] [NVARCHAR](20) NOT NULL,
        PRIMARY KEY ([Id])
    );

    SET @CounterTwo = 1;
    WHILE(@CounterTwo <= 10)
    BEGIN

        DECLARE @SerialNumber NVARCHAR(20);
        SET @SerialNumber = 'SRL: ' + CONVERT(NVARCHAR(10), @GlobalCounter);

        INSERT INTO @SerialInfo([SNumber]) VALUES (@SerialNumber);

        SET @GlobalCounter = @GlobalCounter + 1;
        SET @CounterTwo = @CounterTwo + 1;
    END

    SET @CounterTwo = 1;
    WHILE(@CounterTwo <= 10)
    BEGIN
        SET @SerialNumber = '';
        SELECT @SerialNumber = [SNumber] FROM @SerialInfo WHERE [Id] = @CounterTwo;
        PRINT @SerialNumber;
        SET @CounterTwo = @CounterTwo + 1;
    END

    SET @CounterOne = @CounterOne + 1;
END

Kindly assist me in terms of Global Variable Scope and its value. How to achieve the Expected Results

SRL: 1
SRL: 2
SRL: 3
.
.
.
SRL: 99
SRL: 100

Resultant Image

not sure what the problem is when i check to see whats in the results are in @serialinfo I get the expected results you posted. we will need to know what results you are getting. — ttallierchio
– ttallierchio, Commented Sep 18, 2017 at 12:25
@Jonathan I added a Screenshot which contains the Result of the above said SQL Query. — user8626082
– user8626082, Commented Sep 18, 2017 at 12:37
The problem isn't the global counter - it's the declaration of the table variable. If you do select * from @SerialInfo after the loop you should see the problem. There's probably a better way to do this entire job without loops or table variables, if you could describe the overall objective, which I assume this is part of a proposed solution. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Sep 18, 2017 at 13:00

Damien_The_Unbeliever · Accepted Answer · 2017-09-18 13:15:46Z

In T-SQL, variable declarations do not participate in control flow. So just because declare @SerialInfo table... appears inside the loop, that doesn't mean that @SerialInfo is reinitialized as an empty table each time through the loop.

As soon as a variable declaration is encountered as each batch is being parsed, the variable name becomes valid from that point onwards until the end of the batch.

E.g. this works:

if 1=0
begin
    declare @a int
end
set @a = 15
print @a

Unfortunately, your current code is so procedurally based that it's difficult to see what actual task you're trying to accomplish and make a recommendation for fixing it. But it probably doesn't require loops and relying on generated IDENTITY values.

Hopefully, from the above, it's clear what you're seeing - @SerialInfo ends up containing 100 rows with all of the values you apparently wanted. But since your print loop only queries the first 10 rows of the table, it continues to output the same 10 rows that were inserted the first time that the outer loop ran.

marc_s · Accepted Answer · 2017-12-01 06:33:42Z

0

The thing is with that query you provided, that you are querying only first 10 [SNumber]

WHILE(@CounterTwo <= 10)
BEGIN
    SET @SerialNumber = '';
    SELECT @SerialNumber = [SNumber] FROM @SerialInfo WHERE [Id] = @CounterTwo;

Check that query if it matches your business logic:

SET NOCOUNT ON;
DECLARE @GlobalCounter = 1;
DECLARE @CounterOne INT = 1;
DECLARE @CounterTwo INT = 1;

WHILE(@CounterOne <= 10)
BEGIN
    DECLARE @SerialInfo table
    (
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [SNumber] [NVARCHAR](20) NOT NULL,
        PRIMARY KEY ([Id])
    );

    SET @CounterTwo = 1;
    WHILE(@CounterTwo <= 10)
    BEGIN

        DECLARE @SerialNumber NVARCHAR(20);
        SET @SerialNumber = 'SRL: ' + cast(@globalCounter as varchar)

        INSERT INTO @SerialInfo([SNumber]) VALUES (@SerialNumber);
        SET @GlobalCounter = @GlobalCounter + 1;
        SET @CounterTwo = @CounterTwo + 1;
    END
    SET @CounterOne = @CounterOne + 1;
END
set @CounterTwo = 1
WHILE(@CounterTwo <= @GlobalCounter)
BEGIN
    SET @SerialNumber = '';
    SELECT @SerialNumber = [SNumber] FROM @SerialInfo WHERE [Id] = @CounterTwo;
    PRINT @SerialNumber;
    SET @CounterTwo = @CounterTwo + 1;
END

edited Dec 1, 2017 at 6:33

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Sep 18, 2017 at 12:30

Karolina Ochlik

9486 silver badges8 bronze badges

1 Comment

user8626082 Over a year ago

Not at all. The '@SerialInfo' table variable always contains 10 records.

Collectives™ on Stack Overflow

SQL Server - Global Variable Resets in Nested While Loop

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related