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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With