Offset & Fetch Next will be ok to Use in a Loop?

user_3725288 0
Viewed 3488

I need to write a loop to pass data which are on a SQL temporary table to a stored procedure one by one. Is it OK to use the below approach? Does it cost lots of memory?

DECLARE @SomeData AS TABLE(
    Id      INT,
    Name    VARCHAR(MAX),
    Age     INT
)

INSERT INTO @SomeData
        ( Id, Name, Age )
VALUES  ( 1, 'AAAAA', 10 ),
        ( 2, 'BBBBB', 12 ),
        ( 3, 'CCCCC', 13 ),
        ( 4, 'DDDDD', 14 ),
        ( 5, 'EEEEE', 15 );

DECLARE @iterator INT = 0

WHILE @iterator< (SELECT COUNT(Id) FROM @SomeData)
BEGIN
    SELECT * 
    FROM @SomeData
    ORDER BY Id 
    OFFSET @iterator ROWS
    FETCH NEXT 1 ROWS ONLY

    SET @iterator +=1


    /*Execute a Stored Procedure*/
END
stored-procedures sql-server sql loops
1 Answers

I think you don't need to (SELECT COUNT(Id) FROM @SomeData) again after while, if table SomeData is a big data it will slow. You can try like this:

DECLARE @iterator INT = 0
declare @n int = (SELECT COUNT(Id) FROM @SomeData)
WHILE @iterator< @n
BEGIN
    SELECT * 
    FROM @SomeData
    ORDER BY Id 
    OFFSET @iterator ROWS
    FETCH NEXT 1 ROWS ONLY

    SET @iterator +=1


    /*Execute a Stored Procedure*/
END
user_80630480
Related