SQL Server Nested Cursors and Variables Declaration

Question

I have a doubt regarding the variable declaration in a nested cursors scenario.

This is an small nested cursor sample that i found. In other samples I've seen I also find DECLARE clauses inside the first cursor.

DECLARE @ClientID int;
DECLARE Cur1 CURSOR FOR SELECT ClientID From Folder;
OPEN Cur1
FETCH NEXT FROM Cur1 INTO @ClientID;
SELECT @FETCH_Cur1 = @@FETCH_STATUS
WHILE @FETCH_Cur1 = 0
BEGIN

    DECLARE @UID int;
    DECLARE Cur2 CURSOR FOR SELECT UID FROM Attend Where ClientID=@ClientID;
    OPEN Cur2;
    FETCH NEXT FROM Cur2 INTO @UID;
    SELECT @FETCH_Cur2 = @@FETCH_STATUS
    WHILE @FETCH_Cur2 = 0
    BEGIN

        PRINT 'Found UID: ' + Cast(@UID as Varchar);
        
        FETCH NEXT FROM Cur2 INTO @UID;
        SELECT @FETCH_Cur2 = @@FETCH_STATUS
    END;
    CLOSE Cur2;
    DEALLOCATE Cur2;
    FETCH NEXT FROM Cur1 INTO @ClientID;
    SELECT @FETCH_Cur1 = @@FETCH_STATUS
END;
PRINT 'DONE';
CLOSE Cur1;
DEALLOCATE Cur1;

The code works, but my doubt is if it's correct the DECLARATIONS inside the first cursor.

DECLARE @UID int;

Shouldn't Declarations be placed at the beginning of code, as is normally done for other programming languages?

Answer 1

You can DECLARE a variable inside a WHILE, yes; the latter DECLAREs will simply be ignored. If you declared the variable and assigned it a value at the time (for example DECLARE @UID int = 1; it would be assigned 1 in each iteration:

DECLARE @I int = 1;

WHILE @i < 10 BEGIN

    DECLARE @W int;

    SET @W = ISNULL(@W,1) + 1;

    SET @I = @I + 1
END

SELECT @W; --10
GO

DECLARE @I int = 1;

WHILE @i < 10 BEGIN

    DECLARE @W int = 0;

    SET @W = ISNULL(@W,1) + 1;

    SET @I = @I + 1
END

SELECT @W; -- 1

DB<>fiddle

Of course, I personally prefer to DECLARE the variables outside of the WHILE as I feel the code is "cleaner", but that doesn't mean you have to.