Melbourne, Australia
To be provided
Use Contact to message

SQL For Loop or While

Harmik Batth Tech's Blog

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 float

SELECT Distinct  ComputerID, ComputerName from ComputerNamesTable
WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @freeAVG=avg(AverageValue), @freeMIN=min(MinValue), @freeMAX=max(MaxValue)
FROM AllData
WHERE ComputerRowID = @ComputerID
group by ComputerRowID

Print @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

Add your comment