1. “Do I always need to use a BEGIN…END statement block within an IF statement?”
2. “When should I include an ELSE clause within an IF statement?”
3. How do I nest IF and IF…ELSE statements, if it’s even possible?”
4. “Is it possible to terminate a procedures execution from within an IF statement?”
5. “Which is better to use, a CASE expression or an IF (or IF…ELSE) statement?”
6. “Can I use control-of-flow language to do an upsert in SQL Server?”
7. “I don’t get how a WHILE loop works. Can you help make sense of what’s going on here?”
8. “How do I add a pause or wait time to my T-SQL in order to delay execution?”
9. “Is it possible to skip from one section of your T-SQL code to another?”
10. “What’s the simplest way to incorporate error handling into my stored procedures?”

“Do I always need to use a BEGIN...END statement block within an IF statement?”

You need to include the BEGIN...END block only if you want to associate more than one T-SQL statement with the IF condition. The condition is a Boolean expression within the IF clause that directs statement execution control flow. The query engine evaluates the condition and, if it evaluates to TRUE, runs the statement immediately following the IF clause. Otherwise, the query engine skips ahead to the appropriate next statement.

If you want that condition to apply to multiple statements, you must treat them as a statement block by enclosing them within the BEGIN and END keywords. Let’s look at a few IF statements in action to better understand how the execution flow works. The following stored procedure includes the logic necessary to check whether the @CustID variable is NULL:

The IF expression verifies whether the variable value is NULL. If it is, the expression evaluates to TRUE, and the query processor executes the PRINT statement. Otherwise, the processor skips the PRINT statement and jumps to the SELECT statement. We can verify this by calling the procedure and passing in a valid BusinessEntityID value:

In this case, the EXECUTE statement (and subsequently the procedure) returns the results shown in the following table:

FirstName	LastName	City	StateProvinceName
Rebecca	Robinson	Seaford	Victoria

Next let’s call the procedure without specifying a BusinessEntityID value:

Because the @CustID value is NULL, the query engine executes the PRINT statement and returns the following message, along with an empty result set for the customer data:

So far, this is all fairly straightforward, but now suppose our procedure includes additional statements that should run if the IF expression evaluates to TRUE:

This time around, we want to run two PRINT statements as well as a SELECT statement that returns a list of sales representations. When we call the stored procedure without specifying a BusinessEntityID value, we receive the two messages, as expected:

In addition, we receive the following information about the sales reps:

FirstName	LastName	TerritoryName	EmailAddress
Michael	Blythe	Northeast	michael9@adventure-works.com
Linda	Mitchell	Southwest	linda3@adventure-works.com
Jillian	Carson	Central	jillian0@adventure-works.com
Garrett	Vargas	Canada	garrett1@adventure-works.com
Tsvi	Reiter	Southeast	tsvi0@adventure-works.com
Pamela	Ansman-Wolfe	Northwest	pamela0@adventure-works.com
Shu	Ito	Southwest	shu0@adventure-works.com
José	Saraiva	Canada	josé1@adventure-works.com
David	Campbell	Northwest	david8@adventure-works.com
Tete	Mensa-Annan	Northwest	tete0@adventure-works.com
Lynn	Tsoflias	Australia	lynn0@adventure-works.com
Rachel	Valdez	Germany	rachel0@adventure-works.com
Jae	Pak	United Kingdom	jae0@adventure-works.com
Ranjit	Varkey Chudukatil	France	ranjit0@adventure-works.com

Plus, we receive an empty result set for the customer information. Now let’s look what happens when we call the procedure and pass in a correct BusinessEntityID value:

As expected, we won’t receive the message about providing a customer ID, but we’ll still receive the message about contacting a sales rep as well as the result set that contains the sales rep information. Only then will we receive the result set that contains the customer information, which is the only data we really wanted.

The problem, of course, is that the query engine associates only the first PRINT statement with the IF condition. If we want to associate additional statements, we must enclose all the statements within a BEGIN...END block:

If we now call the stored procedure without specifying a customer ID, we’ll receive both messages, a result set that contains the sales rep information, and an empty result set for the customer information. However, if we do provide a valid customer ID, the query will return only the customer information.

One other note about using BEGIN...END. Many developers will enclose even a single statement in BEGIN...END because it makes the code more readable and makes it less likely that another developer will introduce an error by adding a statement without the block. In general, it doesn’t hurt to include the BEGIN...END block even if you don’t need it in a particular case. In the long run it could save everyone a lot of trouble.

“When should I include an ELSE clause within an IF statement?”

You can add an ELSE clause to any IF construction when you want to run specific statements in the event the Boolean expression (the IF condition) evaluates to FALSE. You specify the ELSE clause after the IF statement block. An ELSE clause let’s you better control your code’s logic and provides a mechanism for running statements outside of the IF...ELSE structure.

Let’s again rewrite our stored procedure. This time, we’ll put the logic for retrieving the customer information in an ELSE clause and then add a final PRINT statement after the IF...ELSE block:

As in the previous example, if the IF expression evaluates to TRUE, the statements within the BEGIN...END block will run. However, this time around, if the expression evaluates to FALSE, the SELECT statement in the ELSE clause will run. This approach let’s us be more precise with our logic while at the same time add other statements that are unrelated to the IF...ELSE statements.

In this case, we’ve added a final PRINT statement, which will run regardless of what statements run within the IF...ELSE blocks. That means, when we call the function and pass in a valid customer ID, the procedure will return only the customer data along with the following information:

However, if we call the procedure without specifying a customer ID, it will return the original two statements about providing a customer ID and contacting the sales rep as well as return the result set containing the sales rep information. The results will also include the statement about AdventureWorks Bicycles, but will not include the empty result set for the customer information. Because we’ve added the ELSE clause, the clause’s SELECT statement is no longer executed when the IF expression evaluates to TRUE. The query engine executes the ELSE clause only if the related IF expression evaluates to FALSE.

“How do I nest IF and IF…ELSE statements, if it’s even possible?”

Yes, it is possible to nest your IF and IF...ELSE statements, and you can do so to whatever degree necessary and practical. The primary limitation on nesting is available memory.

To demonstrate how nesting works, let’s return to the GetCustomerInfo stored procedure. As you’ll recall from the last couples examples, we used the IF conditions to determine which statements to run based on the value of the @CustID variable. If the value was NULL, we ran one set of statements. If the value was an integer, we ran a different statement. However, what if the value is a valid integer but not a valid customer ID?

To address this situation, we can add logic to our T-SQL code that handles the additional scenario. One way to do this is to nest an IF...ELSE statement within the outer ELSE clause, as shown in the following example:

Notice we use the EXISTS function to check whether the ID exists. As a result, the SELECT statement in the original ELSE clause will run only if the @CustID value matches a value in underlying table. For example, suppose we pass in the value 101 when we call the stored procedure. If there is no match, it will return only the following message:

Because the first IF expression evaluated to FALSE, the ELSE clause kicked in. However, the nested IF expression in the outer ELSE clause also evaluated to FALSE, so the query engine jumped to the nested ELSE clause and executed that PRINT statement.

Clearly, the ability to nest IF...ELSE statements provides a powerful mechanism for adding complex logic to our stored procedures and batches. Be wary of overdoing it, however. Memory is not the only issue to keep in mind. Too much conditional logic can result in the query optimizer having to continuously re-cache the plan, which can severely impact performance. Complex logic is often better suited to the application layer, letting each stored procedure focus on a single task.

“Is it possible to terminate a procedure’s execution from within an IF statement?”

Yes, it is possible, and often it’s a good strategy to do so. Why cause the query engine to do any more work than necessary? The sooner you can pull out, the better. And T-SQL supports a great tool for unconditionally exiting a batch, statement block, or stored procedure: the RETURN statement.

You can add a RETURN statement to whatever part of the T-SQL logic that you want to end when and if you reach that part of the code. Let’s return to a basic version of our GetCustomerInfo stored procedure:

Even if the IF expression evaluates to TRUE, the final SELECT statement runs and with it comes an empty result set. One way to avoid this, as we saw earlier, is to add an ELSE clause. However, we can also terminate the stored procedure after the IF statement block runs by adding the RETURN statement:

If the IF expression evaluates to TRUE all the statements within the BEGIN...END block will run, just like before; however, when the query engine hits RETURN, it will immediately stop processing the stored procedure and exit. No other statements will be executed.

Although this might not always be the strategy you want to employ for controlling your code’s logical flow, when it is, the RETURN statement is easy to implement and can be used anywhere in your code that makes sense. For example, the following procedure definition also includes RETURN in the nested IF statement:

As before, the query engine terminates the procedure whenever it encounters RETURN and does not run any more statements.

“Which is better to use, a CASE expression or an IF (or IF…ELSE) statement?”

This question comes up often, in part because Microsoft documentation used to group CASE in with its control-of-flow language elements. But that has changed because CASE is not a control-of-flow language element, but rather a method for evaluating individual expressions.

So it’s not quite fair to compare IF and CASE. They serve different purposes and perform different types of operations. The IF statement controls the flow of your code’s logic; whereas, CASE is used as part of an expression to evaluate a particular value. One way to think of this is it that IF works at the statement level and CASE works at the value level, usually within a SELECT clause or WHERE clause. The CASE expression evaluates a set of conditions sequentially and stops when one of the conditions evaluates to TRUE. With an IF or IF...ELSE statement, you can better control which elements get executed and in what order that execution occurs.

That said, the two can sometimes be used to achieve the same results. For example, the following T-SQL code declares a variable and uses an IF statement to verify it’s value:

Each IF condition is evaluated until one evaluates to TRUE. The query engine will run the associated PRINT statement and RETURN statement, thus ending execution. But we can achieve the same results by creating a CASE expression within a SELECT clause:

Once again, the query engine starts by evaluating each condition, but stops when it reaches the condition that evaluates to TRUE. Even with this simple example, you can see how much easier it is to create a CASE expression. If you can use CASE, that’s a good way to go. But if you need to control the flow of the statement logic, you should use IF or another control-of-flow language construct. A CASE expression is great for doing the job it was meant for, but it’s not meant to control statement execution.

Rather than comparing whether CASE is better than IF, or vice versa, think in terms of picking the right tool for the right job. Usually, you’ll want to use CASE to evaluate and return individual values and use IF to control the flow of statement execution.

“Can I use control-of-flow language to do an upsert in SQL Server?”

For those not familiar with the term, upsert refers to code that includes logic to either update a row or insert a row, depending on whether that row exists. As with other data modification operations, you can use control-of-flow statement elements to do an upsert.

One of the most common ways to do this is to use an IF expression to check for the row’s existence and then execute your statements accordingly. Let’s start with a simple temporary table to demonstrate how this works:

The IF expression uses the EXISTS function to determine whether the row exists (based on the product ID). If it does exist, the UPDATE statement is executed. Otherwise, the database engine performs the INSERT statement defined in the ELSE clause.

In this case, the value does exist so the ListPrice value is updated and the SELECT statement returns a value of 388.88, as expected. Had we set the value of the @ProdID variable to one that doesn’t exist, a new row would have instead been inserted into the table.

This, of course, is a very simple example, but it demonstrates what is a common approach to using an IF...ELSE statement to perform an upsert. And you can just as easily use control-of-flow language for other types of data modifications.

But you need to be careful when you start to mix-and-match in this way. For example, the solution above could result in concurrency issues, either in the form of deadlocks or multiple insertions of the same data. In addition, this approach might not perform as well as other options because of unnecessary table or index scans. Workarounds to this approach might include using the @@rowcount system variable to test whether the update has worked or using a MERGE statement to join the table to itself. The point is, you must be especially diligent when using control-of-flow language to modify data.

“I don’t get how a WHILE loop works. Can you help make sense of what’s going on here?”

The WHILE loop provides a structure for repeatedly executing a set of statements as long as the loop’s condition evaluates to TRUE. The WHILE loop lets you add the logic necessary to limit the number of executions when you don’t know that number in advance. Let’s look at a few examples to get a sense of how these loops work.

We’ll start simply by first demonstrating the logic behind the execution flow when using a WHILE loop. The following T-SQL declares a variable and then executes a loop based on the variable’s value:

The loop begins with the WHILE keyword, followed by the condition that is evaluated each time the loop runs. If the condition evaluates to TRUE, the query engine executes the T-SQL in the statement block that follows the condition. If the condition evaluates to FALSE, the query engine exits the loop and continues on to any code after the statement block.

In this case, the condition will evaluate to TRUE five times, which means the loop will run five times. When the variable value exceeds 104, the loop ends. The following statement shows the results that the statements return:

You can further control the logic within your loop by adding a BREAK statement in any place where the query engine should exit the loop:

This time around, we’ve added an IF statement that specifies that the query engine should break out of the loop if the variable value equals 103. Because we’ve added the IF and BREAK conditions, our loop now returns only the following results;

As you can see, query execution stopped when the variable value hit 103.

You can also add a CONTINUE statement to your WHILE loop to further control the execution. The CONTINUE statement causes the query engine to restart the loop from wherever you’ve added CONTINUE:

Because I’ve included the CONTINUE statement where I did, the statement will never reach the final IF block. Instead, each time the WHILE condition evaluates to TRUE, the statement block will be executed up to CONTINUE and then start over, giving us the following results:

Of course, all we’ve done is go full circle, receiving the same results we received without using CONTINUE and BREAK, but these simple examples should help demonstrate the basic concepts of a WHILE loop.

But now suppose we want to do something a little more involved. Let’s start by creating a small temporary table based on data from the Product table in the AdventureWorks2014 database:

The SELECT statement returns the results shown in the following table:

ProductID	ProductName	Wholesale	Retail
749	Road-150 Red, 62	2171.2942	3578.27
750	Road-150 Red, 44	2171.2942	3578.27
751	Road-150 Red, 48	2171.2942	3578.27
752	Road-150 Red, 52	2171.2942	3578.27
753	Road-150 Red, 56	2171.2942	3578.27
771	Mountain-100 Silver, 38	1912.1544	3399.99
772	Mountain-100 Silver, 42	1912.1544	3399.99
773	Mountain-100 Silver, 44	1912.1544	3399.99
774	Mountain-100 Silver, 48	1912.1544	3399.99
775	Mountain-100 Black, 38	1898.0944	3374.99
776	Mountain-100 Black, 42	1898.0944	3374.99
777	Mountain-100 Black, 44	1898.0944	3374.99
778	Mountain-100 Black, 48	1898.0944	3374.99

Now suppose we want to increase the retail prices in 10% percent increments, but only as long as the average net price remains under $2500. At the same time, the retail price must always remain under $4999.99. We can create a WHILE loop that increments the price while ensuring we stay within our parameters:

The WHILE condition checks whether the average net price is under $2500. If the condition evaluates to TRUE, the statement block runs and increases the retail prices by 10%. The statement block also includes an IF statement, which checks whether the highest retail price is under $4999.00. If it is, the loop starts over. Should the IF condition evaluate to FALSE, the query engine breaks out of the loop and jumps to the final PRINT statement.

I included the @count variable only to count the number of times the WHILE loop runs, which in this case, is three times, as the following results show:

If we were now to view the data in the #PriceyProducts table, we would see the following results:

ProductID	ProductName	Wholesale	Retail
749	Road-150 Red, 62	2171.2942	4762.6774
750	Road-150 Red, 44	2171.2942	4762.6774
751	Road-150 Red, 48	2171.2942	4762.6774
752	Road-150 Red, 52	2171.2942	4762.6774
753	Road-150 Red, 56	2171.2942	4762.6774
771	Mountain-100 Silver, 38	1912.1544	4525.3867
772	Mountain-100 Silver, 42	1912.1544	4525.3867
773	Mountain-100 Silver, 44	1912.1544	4525.3867
774	Mountain-100 Silver, 48	1912.1544	4525.3867
775	Mountain-100 Black, 38	1898.0944	4492.1117
776	Mountain-100 Black, 42	1898.0944	4492.1117
777	Mountain-100 Black, 44	1898.0944	4492.1117
778	Mountain-100 Black, 48	1898.0944	4492.1117

We can verify our results by running the following SELECT statement to determine the current average net price:

The statement returns the value 2598.9164, which exceeds the $2500. It was at this point that the query engine exited the WHILE loop. In addition, as we can see in the results, the maximum Retail value is 4762.6774, which is below the 4999.99 limit.

“How do I add a pause or wait time to my T-SQL in order to delay execution?”

SQL Server supports a handy control-of-flow statement called WAITFOR, which stops code execution until a specified time or for a specified amount of time. A WAITFOR statement can be handy to deal with those unusual circumstances when you need to work around legacy systems or network limitations. For example, one of your SQL Server jobs might trigger a second job that can run only at specific time because of network constrains.

WAITFOR can also be used in development and testing environments to help generate specific responses. For example, suppose we’ve created the following temporary table:

Now suppose we want to test concurrency by running two separate operations, each in its own session, to verify whether we’ll get a dirty read. We can start the first session, but include a WAITFOR statement to delay the transaction by five seconds:

The WAITFOR statement includes the DELAY keyword to indicate that we want to delay the execution, rather than use the TIME option to specify a specific time. During these five seconds, we run a SELECT statement from our second session, but first set the isolation level to READ UNCOMMITTED:

Because the first transaction is still active, the SELECT statement returns a value of 1717.80, the value that has been updated in the first session. However, if we run the SELECT statement again, after the first transaction completes (and rolls back), the statement returns the value 1431.50. In other words, the first time we ran our SELECT statement we received a dirty read.

We can also specify the exact time we want to end our delay by using the TIME option:

Once again, we’re waiting only five seconds, but you get the point. You can provide a time rather than a range, giving you another way to control execution.

“Is it possible to skip from one section of your T-SQL code to another?”

In T-SQL you can use a GOTO statement to jump to another part of the code, as long as you’ve assigned a label to the destination. A label is merely an identifier that provides a heading for the targeted section of code. For example, the following T-SQL includes several labeled blocks of code with several references to them:

When you reference a label, you provide only the label name along with the GOTO keyword. However, when you assign a label to a bock of code, you must also include a colon. When the query engine encounters a GOTO statement, it jumps to the label identified in the statement. For example, the initial IF statement includes a GOTO statement that points to the goto_one label. As a result, if the @count variable has a value of 1, the query engine will jump to the goto_one label, execute the PRINT statement associated with that label, and return the following results.

Of course, you’re likely to want to use the GOTO statement for more practical purposes than in the example above. For example, suppose your T-SQL includes a number of IF statements that take different steps. Regardless of which condition evaluates to TRUE, you want to run a final INSERT statement that logs the event. You can do something similar to the following:

For each possible condition, the statement block ends with a GOTO statement that points to the log_usage label, so no matter what happens elsewhere, the last statement here will run. Although we’ve included only a simple INSERT statement with out label, you can use this strategy to avoid having to recode more complex logic in multiple places.

“What’s the simplest way to incorporate error handling into my stored procedures?”

Since the release SQL Server 2005, error handling has been fairly easy to incorporate in your T-SQL code through the use of TRY...CATCH blocks. The idea behind this is that you put the main body of your code in the TRY block and put the error handling in the CATCH block. The statements in the CATCH block will run only if the query engine encounters an error in the TRY block.

When Microsoft first added support for TRY...CATCH error handling, you had to use a RAISERROR statement to catch and throw the error, should one occur. But that changed in SQL Server 2012. You can now use a THROW statement, which is simpler to use and more accurate.

To demonstrate how all this works, let’s start with a simple temporary table and add a few rows of data:

Now let’s create a stored procedure that adds a row to the table, with the values coming from two input parameters:

First, notice that the procedure definition includes the TRY...CATCH blocks. In the TRY block, we include the primary T-SQL needed to begin the transaction, insert the vales into the table, and commit the transaction. If the statements in the TRY block run with no problem, the operation completes and the database engine exists the TRY...CATCH blocks. However, if the TRY block generates an error, the database engine skips to the CATCH block where the error can be handled.

In this example, the CATCH block includes three statements. The first is an IF statement that rolls back the transaction if the @@TRANCOUNT system variable value is greater than 0, which means a transaction is active in the current session. Next, it includes a PRINT statement, which I’ve included primarily to verify that the CATCH block statements are being executed should an error occur in the TRY block. Finally, I add a THROW statement to catch and return the error.

Let’s look at the stored procedure in action to demonstrate how everything works. First, we’ll use the procedure to add a row to our temporary table and then run a SELECT statement:

Not surprisingly, the query returns four rows, including a row for product 104, as shown in the following results:

ProdID	ListPrice
101	199.99
102	299.99
103	399.99
104	499.99

Now let’s try to run the same EXECUTE command again:

Because we’d be violating the primary key by adding this row, the query engine baulks and returns the following results:

The stored procedure generated the following error:

Notice that the results include the message from the PRINT command, along with the error details provided by the THROW statement.

The THROW statement also let’s you define your own error number, message, and state, as shown in the following example:

If we were to once again try to add product 104, out results would now look like the following:

It’s up to you whether to go with the generic message or create your own. The THROW statement on its own is a powerful tool, and that might be all you need. There is more to error handling, though, so you might benefit from an article I wrote a while back on the subject: “Handling Errors in SQL Server 2012.”