Disclaimer: I am a consultant at Amazon Web Services, and this is my personal blog. The opinions expressed here are solely mine and do not reflect the views of Amazon Web Services (AWS). Any statements made should not be considered official endorsements or statements by AWS.
Let's assume you want to loop through a table variable having the structure like below.
DECLARE @tempCategories TABLE ( Id INT, CategoryName NVARCHAR(500) )
To iterate through this table, just add one more column (
Processed) in this table variable. Now the table should look like this.
DECLARE @tempCategories TABLE ( Id INT, -- Make sure, you have one unique column in the table CategoryName NVARCHAR(500), Processed INT DEFAULT 0 -- This is the main column that will help us in looping through the table. )
Important: Make sure, you have one unique column in the table. If it is
Idthen very good, else create a new one.
After that, use the below SQL.
-- 1. Declare a variable to hold unique column value of table variable DECLARE @Id INT -- 2. Run code till even a single record is present which is un-processed WHILE (SELECT COUNT(*) FROM @tempCategories WHERE Processed = 0) > 0 BEGIN -- A. Select first un-processed item from the table variable -- Note: Here, you can fetch any column as per your need SELECT TOP 1 @Id = Id FROM @tempCategories WHERE Processed = 0 -- B. Do some processing here with your custom logic -- Your custom logic -- Your custom logic -- Your custom logic -- C. Finally, update the table variable, set the 'Processed' column of the processed row to 1. UPDATE @tempCategories SET Processed = 1 Where Id = @Id END
That's it 😊.