Loops are one of the most basic, still very powerful concepts in programming – the same stands for SQL Server loops. Today, we’ll give a brief info on how they function and introduce more complex concepts in upcoming articles of this series.

Introduction

We won’t use any data model in this article. Although this might sound weird (well, we’re working with a database, and there is no data!?), you’ll get the point. Since this is the intro article on SQL Server loops, we’ll cover basic concepts that you could combine with data to get the desired result.

But, first of all – let’s see what loops are. Loops are the programming concept that enables us to write a few lines of code and repeat them until the loop condition holds.

Almost all programming languages implement them, and we’ll usually meet these 3 types of loops:

• WHILE – While the loop condition is true, we’ll execute the code inside that loop
• DO … WHILE – Works in the same manner as the WHILE loop, but the loop condition is tested at the end of the loop. WHILE loops and DO … WHILE loops are very similar and could easily simulate each other. REPEAT … UNTIL (Pascal) is similar to DO … WHILE loop and the loop shall iterate until we “reach” that condition
• FOR – By definition, this loop shall be used to run code inside the loop for the number of times you’ll exactly know before this loop starts. That is true in most cases, and such a loop (if available) should be used in such a manner (to avoid complicated code), but still, you could change the number of times it executes inside the loop

For us, the most important facts are:

• SQL Server implements the WHILE loop allowing us to repeat a certain code while the loop condition holds
• If, for any reason, we need other loops, we can simulate them using a WHILE loop. We’ll show this later in the article
• Loops are rarely used, and queries do most of the job. Still, sometimes, loops prove to be very useful and can ease our life a lot
• You shouldn’t use loops for anything you like. They could cause serious performance issues, so be sure you know what you’re doing

IF … ELSE IF and PRINT

Before we move to loops, we’ll present two SQL statements/commands – IF (IF … ELSE) and PRINT.

IF statement is pretty simple, right after the IF keyword, you’ll put the condition. If that condition evaluates, the block of statements shall execute. If there is nothing else, that’s it.

You could also add ELSE to the IF statement, and this will result in the following – if the original condition wasn’t true, the code in the ELSE part should execute.

If we want to test multiple conditions, we’ll use, IF (1^st condition) … ELSE IF (2^nd condition) … ELSE IF (n-th condition) … ELSE. We’ll do exactly that in our example – just to show how it works in SQL Server.

But before that – the PRINT command. PRINT simply prints the text placed after that command. That is inside quotes, but you could also concatenate strings and use variables.

With the set of commands above, we’ve:

• Declared two variables and assigned values to them
• Used the IF … ELSE IF statement to test which variable is greater

While this example is pretty simple (and it is obvious which number is greater), it’s a nice and simple way to demonstrate how IF … ELSE IF and PRINT work in SQL Server.

Now we’ll use statements from the previous example to show one more thing.

You can notice that we’ve placed the IF statement inside another IF statement. This is called nested IF. You could avoid it by using logical operators in the 1^st IF statement, but this way, the code is more readable).

The goal of our code is to compare two numbers and also print if the first one is greater than 75, greater than 50, or less or equal to 50 (and only in case if the first number is greater than the second number).

Similarly to the previous example, this code is not very “smart” but used to show the concept of nested IF.

SQL Server Loops

Now we’re ready to move to SQL Server loops. We have 1 loop at our disposal, and that is the WHILE loop. You might think why we don’t have others too, and the answer is that the WHILE loop will do the job. First, we’ll take a look at its syntax.

WHILE {condition holds}
BEGIN
{…do something…}
END;

As you could easily conclude – while the loop conditions are true, we’ll execute all statements in the BEGIN … END block. Since we’re talking about SQL Server loops, we have all SQL statements at our disposal, and we can use them in the WHILE loop as we like.

Let’s now take a look at the first example of the WHILE loop.

We’ve declared the variable @i and set it to 1. If the current value of the variable is <= 10, we’ll enter the loop body and execute statements. Each time we enter the body, we’ll increase @i by 1. That way, the value of variable @i will become 10 at some point, and that will prevent the loop from running over and over again.

Few things are important to mention here:

• The @i variable counts how many times we were in the loop, and sometimes the word “counter”, shall be used for such variable. Naming the counter @i is also a good practice
• Since we know that this loop shall always execute exactly 10 times – @i starts from 1, increase by 1, and we’ll repeat that until @i becomes 11 – this is also the simulation of the FOR loop using the WHILE loop
• It’s always important to be sure that the loop condition won’t always be true. If the loop condition always holds, the loop will be infinite, and, in most cases, we don’t want that (especially in the database)

• Hint: Infinite loops are rarely used in programming. One such case is when we want to wait for a signal (something) to happen. We’ll wait using the loop that never ends. While this is very useful in programming, using such a loop in databases would not be a smart move (we’ll impact performance, and the whole point of databases is to get data out of it as fast as possible).

Two keywords – BREAK and CONTINUE, are present in most programming languages. Same stands for SQL Server loops. The idea is the following:

• When you encounter the BREAK keyword in the loop, you simply disregard all statements until the end of the loop (don’t execute any) and exit the loop (not going to the next step, even if the loop condition holds)
• The CONTINUE acts similar to BREAK – it disregards all statements until the end of the loop, but then continues with the loop

You can notice that placing a BREAK in the loop resulted that we exited the loop in the 9^th pass.

The code above results with an infinite loop. The reason for that is that when @i becomes 9, we’ll CONTINUE the loop, and @i shall never have the value 10. Since this is an infinite loop, it will just spend resources on the server without doing anything. We can terminate the query by clicking on the “stop” button.

After clicking on the stop button, you can notice that the loop did something, printed numbers 1 to 8, and number 9 as many times as it happened before we canceled the query.

SQL Server Loops and Dates

So far, we’ve covered the basics and how SQL Server loops function and how we combine statements like IF and PRINT with loops. Now we’ll use loops to do something useful. We want to print all dates between the two given dates.

We’ve declared two variables, assigned date values to them. The only difference is that we’re using variable @loop_date. While we could do it without this variable, it’s a good practice to keep the original values (in our case, these are @date_start and @date_end) unchanged. In each step of the loop, we’ve printed the date and increased the “counter” by 1 day. This is useful, but still, we can’t use these dates in the query.

To do that, we’ll store values in the temporary table.

We’ve dropped the temporary tables #dates (if it exists). After that, we’ve created a temporary table. The code used is almost the same as in the previous example. The difference is that, instead of using the PRINT command, at each step of the loop, we’ve inserted 1 row in the temporary table.

After the loop, we’ve selected from the temporary table as well dropped it.

Conclusion

SQL Server loops are extremely powerful if you use them as they were intended to be used. Today, we’ve just scratched the surface, but all-important concepts were explained. In the upcoming article, we’ll show more complex examples and combine loops with other database objects.

Table of contents

• Author
• Recent Posts

Emil Drkusic

Emil is a database professional with 10+ years of experience in everything related to databases. During the years, he worked in the IT and finance industry and now works as a freelancer.

His past and present engagements vary from database design and coding to teaching, consulting, and writing about databases. Also not to forget, BI, creating algorithms, chess, philately, 2 dogs, 2 cats, 1 wife, 1 baby...

You can find him on LinkedIn

View all posts by Emil Drkusic

Latest posts by Emil Drkusic (see all)

• Learn SQL: How to prevent SQL Injection attacks - May 17, 2021
• Learn SQL: Dynamic SQL - March 3, 2021
• Learn SQL: SQL Injection - November 2, 2020