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 (1st condition) … ELSE IF (2nd 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.

DECLARE @num1 INTEGER;
DECLARE @num2 INTEGER;

SET @num1 = 20;
SET @num2 = 30;

IF (@num1 > @num2)
  PRINT '1st number is greater than 2nd number.'
ELSE IF (@num2 > @num1)
  PRINT '2nd number is greater than 1st number.'
ELSE 
  PRINT 'The numbers are equal.';

#functions #sql commands #t-sql #sql