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.

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s