SQL For Loop or While
Writing SQL Query is always fun. I have started form single Table query to double or even n number of tables. Joining table is always fun and getting data out of it is even more pleasing.
There is no FOR LOOP available for SQL Server Query. But you can use While Loop to simulate the FOR LOOP.
DECLARE @somevariable1 INT = 0; DECLARE @somevariable2 varchar!50) = ''; WHILE @somevariable1 <= 100 BEGIN {... your statements here...} SET @somevariable1= @somevariable1 + 1; END;
Example as per below:
In this Data is fetched form one table and loop through to get data from another table for first table distinct data or id etc.
DECLARE @ComputerID as int
DECLARE @ComputerName as varchar(120)
DECLARE @freeAVG as float
DECLARE @freeMIN as float
DECLARE @freeMAX as floatSELECT Distinct ComputerID, ComputerName from ComputerNamesTable
WHILE @@FETCH_STATUS = 0
BEGINSELECT @freeAVG=avg(AverageValue), @freeMIN=min(MinValue), @freeMAX=max(MaxValue)
FROM AllData
WHERE ComputerRowID = @ComputerID
group by ComputerRowIDPrint @ComputerID + ‘ – ‘ + @ComputerName + ‘ – ‘ + @freeAVG+ ‘ – ‘ + @freeMIN+ ‘ – ‘ + @freeMAX
FETCH NEXT FROM WComputer INTO @ComputerID, @ComputerName
END
CLOSE WComputer;
DEALLOCATE WComputer;
I hope this is what you are looking for.
Please feel free to provide feedback. Happy Reading.
No Comments