How to use the while loop in SQL Server 2005

If you ever need to loop through data in Microsoft SQL Server it is actually quite easy. This article will provide the basics for using a SQL while loop.

Difficulty: Moderately Easy

Instructions

Things You'll Need:

• SQL Server

1. First let's set up our test case and then we'll explain each piece:
   
   DECLARE @Month int
   Set @Month = 1
   CREATE TABLE #Sales
   (Month int,
   Year int,
   SalesAmount decimal(10,2))
   
   INSERT INTO #Sales
   (Month,
   Year,
   SalesAmount)
   SELECT * FROM
   (SELECT 1 as 'Month', 2009 as 'Year', 24.50 as 'SalesAmount'
   UNION ALL
   SELECT 1, 2009, 19.99
   UNION ALL
   SELECT 1, 2009, 34.99
   UNION ALL
   SELECT 2, 2009, 15.99
   UNION ALL
   SELECT 2, 2009, 17.99
   UNION ALL
   SELECT 2, 2009, 13.49
   UNION ALL
   SELECT 3, 2009, 10.00
   UNION ALL
   SELECT 3, 2009, 5.99) data
   
   WHILE @Month <= 3
   BEGIN
   SELECT * FROM #Sales WHERE Month = @Month
   SET @Month = @Month + 1
   END
   
   DROP TABLE #Sales

2. Our SQL Server example can be broken down into several pieces: 1-Variables, 2-Table Creation, 3-Data Creation, 4-The While Loop, 5-Removing the table.

3. 1-Variables
   
   The first step in our example is the definition of the variables.
   
   DECLARE @Month int
   Set @Month = 1
   
   The word "DECLARE" is telling SQL Server that we are going to use one variable and we are calling that variable Month. The @ sign in front lets SQL Server know that it is a variable. The letters "int" mean that our Month variable is an integer.
   
   The first thing we do is assign a value to our variable using "SET". Now @Month is assigned a value of 1.

4. 2-Table Creation
   
   Next we want to create a temporary table to play with for our example. To do this we use the "CREATE TABLE" command.
   
   CREATE TABLE #Sales
   (Month int,
   Year int,
   SalesAmount decimal(10,2))
   
   To create a table we must give the table a name following the "CREATE TABLE" command. In this case we are giving the table the name "Sales". We place a "#" sign in front of the word to tell SQL Server that this is a temporary table just for use in this query.
   
   Then we define the fields that are in the table by listing the field names and field types. Each field is separated by a comma. Again "int" stands for integer. This time we also have decimal(10,2) which means that the field can hold decimal values. The 10 means the maximum length of the field while the 2 represents how many characters are to the right of the decimal (for example 12345678.90).

5. 3-Data Creation
   
   Now that we have a table we need to put some data into that table. To do this we use the "INSERT INTO" command.
   
   INSERT INTO #Sales
   (Month,
   Year,
   SalesAmount)
   SELECT * FROM
   (SELECT 1 as 'Month', 2009 as 'Year', 24.50 as 'SalesAmount'
   UNION ALL
   SELECT 1, 2009, 19.99
   UNION ALL
   SELECT 1, 2009, 34.99
   UNION ALL
   SELECT 2, 2009, 15.99
   UNION ALL
   SELECT 2, 2009, 17.99
   UNION ALL
   SELECT 2, 2009, 13.49
   UNION ALL
   SELECT 3, 2009, 10.00
   UNION ALL
   SELECT 3, 2009, 5.99) data
   
   After the words "INSERT INTO" we tell SQL Server which table to insert data into.
   
   Then we tell SQL Server what fields in that table we want to put data into.
   
   Finally we gather the data that we want to put into that table. In our case we are making up fictional data so we are selecting it and unioning it together to create a data set for our table. Notice on the first SELECT statement I must define the field names or SQL Server won't allow it. The word "data" at the end is simply a name that was made up to represent the data set. This is only necessary since I am using a sub-select (A select statement that is selecting data from another select statement).

6. 4-The While Loop
   
   Next the purpose of the exercise was to learn how to use a "WHILE" loop. Up to this point all we've done is set up some test data to use the while loop on.
   
   WHILE @Month <= 3
   BEGIN
   SELECT * FROM #Sales WHERE Month = @Month
   SET @Month = @Month + 1
   END
   
   This while loop is about as simple as they come. All we are doing is looping through the table and selecting each month of data one at a time.
   
   First we give the WHILE loop the criteria to keep SQL Server looping through the data. In this case we tell it that as long as @Month is less than or equal to 3 keep running the SQL statement.
   
   Next we must BEGIN the SQL we want to loop through using the word BEGIN.
   
   Then we have our SELECT statement. We are selecting data from #Sales where the month in the table is the same as the variable @Month.
   
   Finally, we have another SET statement that increments the @Month variable by 1 each time it loops through the data. In this manner @Month changes from having a value of 1 to having a value of 2 and then 3.
   
   The word END tells SQL Server that we are done with the SQL we wanted to loop through.
   
   WHEN SQL Server runs through the data the third time it sets @Month = 4 and this causes @Month to be greater than 3 so the WHILE loop stops running. SQL Server then jumps to whatever is after the WHILE loop to continue running.

7. 5-Removing the table
   
   In this last step we remove the temporary table using the "DROP TABLE" command.
   
   DROP TABLE #Sales
   
   We do this to clean up after ourselves. If we didn't add this and tried to run the statement again, SQL Server would fail and give us a message indicating that the table #Sales already existed and we couldn't create it again.

Tips & Warnings

• Be careful when choosing your WHILE criteria and setting the circumstances by which the statement will stop looping. Otherwise you may end up with an infinite loop where SQL Server never meets the WHILE criteria and endlessly loops.