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 with IF ELSE
If you’re like most casual user of T-SQL then you’re you’ve mostly likely copied some queries into a query windows 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.
Tip: Read our Ultimate Guide to Stored Procedures to learn more coding TSQL in your database.
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 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 use plenty in the following sections!
IF ELSE Statement
The IF ELSE statement is a combination of statements which allows us to program whether one set of statements are, 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 to these conditions as we use them often in various built-in functions such as 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 this to avoid a “divide by zero” error.
Keep in mind you can define and use variables and parameters in your stored procedure.
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 affect.
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 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 there are no code blocks.
Nesting IF ELSE
If statements can also be nested. This simply means we’re placing an IF..ELSE statement withing 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.
Check out these other posts that explore stored procedure control of flow topics:
