Use IF…ELSE Statements to Program a Stored Procedure

After reading this article, you will understand the basics of programming a stored procedure using IF…ELSE statements; most importantly how keywords, such as BEGIN, END, IF and ELSE affect the order of execution within your stored procedures.

All the examples for this lesson are based on Microsoft SQL Server Management Studio and the sample databases AdventureWorks and WideWorldImporters. You can get started using these free tools with my Guide Getting Started Using SQL Server.

Programming a Stored Procedure

If you’re like most casual users of T-SQL, then you’ve mostly likely copied some queries into a query window and ran it to see results. The queries were running from top to bottom, one statement after another, in sequence. In simple terms, the control-of-flow of the execution was top to bottom.

This works for queries, but when we want to work with more complex example that involves logic, such as “either or”, then we need to look to other statements for assistance.

BEGIN and END

The BEGIN and END statements are used define a statement block. A statement block is a series of SQL statements that run together. A statement block is also called a batch.

Usage of BEGIN and END become more apparent when we talk about IF…ELSE, but for now, consider this: If a statement is a sentence, then BEGIN and END allow us to define paragraphs.

Here is the general syntax for a BEGIN END block:

BEGIN
   SQL Statement 1
   SQL Statement 2
   ...
END;

The statements within a block are run in sequence; therefore, SQL Statement 1 is run before SQL Statement 2.
When using BEGIN and END, it is best practice to indent the enclosed statement blocks.

Looking for a practical example of using BEGIN and END? Then read on! You’ll see it being used a lot in the following sections!

IF Statement

The IF…ELSE statement is a combination of statements which allow us to program whether one set of statements or another are executed. Each IF statement has a condition. IF the condition evaluates to true, then the statement or statement block is executed. If the condition is FALSE, then the code block after the ELSE is executed.

You should be familiar with these conditions as we use them often in various built-in functions such as IIF and CASE as well as in general within the SELECT statement’s WHERE clause.

The formal for the IF…ELSE statement is:

IF (condition)
BEGIN
   -- code block run when condition is TRUE
END
ELSE
BEGIN
   -- code block run when condition is FALSE
END

IF…ELSE Example

Here is an example within a stored procedure we use to calculate velocity. We use an IF…ELSE statement to test whether time equals zero. We do to avoid a “divide by zero” error.

IF Statement with Code Blocks

If you want, you can try this. First, create the procedure using the following:

CREATE PROCEDURE uspCalcVelocity
                 @distance float,
                 @time float,
                 @velocity float OUTPUT
AS

IF (@time = 0.00)
BEGIN
   -- we can't divide by zero, so assume time is 1 hour
   Select @time = 1;
   SELECT @velocity = @distance / @time;
END
ELSE
BEGIN
   SELECT @velocity = @distance / @time;   
END

You can test the stored procedure with the following EXECUTE statement, just be sure to try different values for time, especially one equal to 0.00 to see its effect.

Declare @v float
EXECUTE uspCalcVelocity 120.00, 2.00, @v OUTPUT
SELECT @v

ELSE Optional

Keep in mind the ELSE statement is optional. Though in some cases, it’s super awesome to have an ELSE, so we can run “this code or that,” in other cases, if we were to always use an ELSE, it would result in more complex logic.

For example, our sample program can be simplified by removing the ELSE. Let’s see how.

ALTER PROCEDURE uspCalcVelocity
                 @distance float,
                 @time float,
                 @velocity float OUTPUT
AS
IF (@time = 0.00)
BEGIN
   -- we can't divide by zero, so assume time is 1 hour
   Select @time = 1;
END
SELECT @velocity = @distance / @time;

So, what changed?

The IF statement's main purpose is now relegated to testing time’s value. Previously, we calculated velocity in each block, now, the calculation is outside of the IF statement.

IF…ELSE with No Blocks Example

Also, if you just need to execute one statement, you can omit the BEGIN END statement blocks.

ALTER PROCEDURE uspCalcVelocity
                 @distance float,
                 @time float,
                 @velocity float OUTPUT
AS
IF (@time = 0.00) Select @time = 1;
SELECT @velocity = @distance / @time;

Here, you can see that there are no code blocks.

Nesting IF…ELSE

If statements can also be nested. This simply means we’re placing an IF..ELSE statement within the code block or another.

The nested IF…ELSE is color coded blue.

DECLARE @a int = 5,
        @b int = 10

IF (@a > 4)
BEGIN
   PRINT '@a > 4'
   IF (@b < 10)
      PRINT '@b < 10'
   ELSE
      PRINT '@b >= 10'
END

When run the following is printed:

@a > 4
@b >= 10

Nested IF…ELSE statements can simplify logic. Consider the example above, without nested IF, we would have to use Boolean logic. Our example becomes:

IF (@a > 4 AND @b < 10)
BEGIN
   PRINT '@a > 4'
   PRINT '@b < 10'
END
IF (@a > 4 AND @b >= 10)
BEGIN
   PRINT '@a > 4'
   PRINT '@b >= 10'
END

Which, I think is harder to maintain — notice how the blue portion is now sprinkled throughout the code. No only is the logic a little harder to read (those inequalities get confusing), but there is a some repeated code. This will make it harder to make changes in the future. We’ll either make a logic mistake or forget to make a change to a statement in two places.

The post Use IF…ELSE Statements to Program a Stored Procedure appeared first on Essential SQL.