This section will deal with ensuring that our stored procedure flows correctly. Although we will cover some T-SQL statements here, as we have indicated earlier, it is not within the scope of the book to teach you how these statements should be coded.
When working with decisions, functions, or loops, there will be many instances when we have to execute more than one line of code. SQL Server has to know when a block of code starts and ends. In programming languages, we will find END IF statements or LOOP … UNTIL structures where there are keywords for defining the beginning and end of the block. This is not the case within SQL Server, which uses a BEGIN … END structure instead.
In the following snippet, we have two similar stored procedures. The difference is in the use of the BEGIN … END code block. Executing the first one will execute the second PRINT statement, as the IF statement will take only the first line as part of the decision:
CREATE PROCEDURE ut_NoBeginEnd AS BEGIN DECLARE @var1 int, @var2 int SET @var1 = 1 SET @var2 = 2 IF @var1 = @var2 PRINT @var1 PRINT @var2 END
Compare it with the following snippet. Here, neither of the PRINT statements will be executed:
CREATE PROCEDURE ut_BeginEnd AS BEGIN DECLARE @var1 int, @var2 int SET @var1 = 1 SET @var2 = 2 IF @var1 = @var2 BEGIN PRINT @var1 PRINT @var2 END END
Now that we know how to demarcate blocks in our code, we can look at the IF statement in more detail.
There are two main ways to make decision in SQL Server. The first one makes decisions throughout our code, whereas the second one bases decisions on data returned in specific columns from the returned rows. There are other statements, such as NULLIF, which will return a NULL value if two expressions are similar, but this isn't decision processing and is more of a comparison. Let's start with the first IF statement.
This is a simple Boolean decision processing strategy, where a statement is evaluated as either TRUE or FALSE. If the statement is TRUE, it will process the statements immediately following the IF. If the statement is FALSE, then it will process the statements following the ELSE statement, if there is one defined. If there is no ELSE statement, then processing will continue after the END of that statement block.
It is possible to evaluate more than a simple a = b type of statement; we can even include SELECT statements under the IF. However, you may find that having a SELECT statement within an IF, rather than placing the value into a variable and then testing it, is not as cost-effective. If we look at the execution plans of the following two statements, we can see the huge difference it makes while processing.
The first statement works faster, as a simple SELECT statement takes the table and processes the data.
DECLARE @AvgFound Money SELECT @AvgFound = AVG(UnitPrice) FROM [Order Details] IF @AvgFound < 10 Print 'Less than 10' ELSE Print 'More than 10'
Here's the execution plan of the above (we will have a more detailed explanation of the execution plan in the next chapter):
Here's the second code snippet:
IF (SELECT AVG(UnitPrice) FROM [Order Details]) < 10 Print 'Less than 10' ELSE Print 'More than 10'
The execution plan for the second statement is more complex:
By letting the cursor hover over some of the details, you can see the underlying processing.
Unlike the IF statement that is placed in code as part of a decision-making process, CASE can be found within any of the SELECT, UPDATE, and INSERT T-SQL data statements.
CASE statements won't work for DELETEs. To implement the CASE statement, two different methods can be employed. The first method is known as the simple CASE function. Here, an expression, which is to be tested, is the same throughout the possible options available. Let's look at its syntax:
CASE input_expression WHEN when_expression THEN result_expression [...n] [ ELSE else_result_expression ] END [AS column_alias]
This will be used, for example, when we have a column containing several values and we want to translate this value into something more meaningful, such as the output in the column. We can also include other SELECT statements. We can see this in action in the following code:
CREATE PROCEDURE ut_CASE_with_SELECT AS BEGIN SELECT OrderId, Discount, CASE Discount WHEN 0 THEN 'No Discount' WHEN (SELECT MAX(Discount) FROM [Order Details]) THEN 'Top Discount' ELSE 'Average' END AS Disc FROM [order details] END
There is a problem concerning the NULL values. In the following T-SQL, the Fax column contains either a fax number or NULL. Here, we test the column to check whether it's equal to another value however, we can't do this for NULL, where the test is ignored:
CREATE PROCEDURE ut_CASE_NULL_NotWorking AS BEGIN SELECT CompanyName, Phone, CASE Fax WHEN NULL THEN 'No Fax' ELSE Fax END AS Fax FROM Customers END
However, by using the second type of CASE statement, we can search for NULL values. Again, let's begin by looking at its syntax:
CASE WHEN Boolean_expression THEN true_result_expression [...n] [ELSE else_result_expression] END
To solve the problem in our previous example, we can code the example as:
CREATE PROCEDURE ut_CASE_With_Null AS BEGIN SELECT CompanyName, Phone,
CASE WHEN FAX Is NULL THEN 'No Fax' ELSE Fax END AS Fax FROM Customers END
As with programming languages, labels are frowned on and it is better to avoid using them. However, there is one exception – it is advisable to use them when you are branching to the end of a procedure for doing some cleanup or error coding. At times, even this can get messy, as you may have to code a GOTO around a GOTO label to avoid error processing. To clarify this, let's examine the following code:
IF @@ERROR > 0 GOTO Error ... GOTO ProcEnd Error: ... ProcEnd: RETURN
It is necessary to use GOTO ProcEnd for avoiding normal processing from slipping into the Error: labelled routine. In this instance it is not too confusing, but it can be, in complex stored procedures.
Now that we know how to make decisions, we can move on to processing information iteratively.
At this point, we will not look at looping through a cursor, as cursors are covered in Chapter 3, which leaves us with one method for looping around a set of code.
To repeat a block of code, SQL Server gives us the WHILE statement. This will continue executing the block and forming the loop, until either the Boolean expression returns FALSE or we BREAK out of the code. The syntax is:
WHILE Boolean_expression { sql_statement | statement_block } [ BREAK ] { sql_statement | statement_block } [ CONTINUE ]
BREAK breaks the loop and passes the control to the statements outside the code block, while the CONTINUE statement will resume processing from the beginning of the loop.
We can see this in the following stored procedure:
CREATE PROCEDURE ut_WhileLoop AS BEGIN DECLARE @var1 int, @var2 int SET @var1 = 1 SET @var2 = 1 WHILE @var1 < 10 BEGIN IF @var2 > 100 BREAK SET @var2 = @var2 + @var2 SET @var1 = @var1 + 1 END PRINT 'Var1=' + CONVERT(CHAR(3),@var1) + ' and Var2=' + CONVERT(CHAR(3),@var2) END
As with the IF statement, its common to see a WHILE statement working with a SELECT criterion.
The WAITFOR function can be used to pause a stored procedure for a certain amount of time. Pausing for a specific time will allow a stored procedure to run at the same time every single day. If you want the procedure to run only on certain days of the week, you can surround the code by a check to ensure that you are on a valid day.
There is a more efficient and controlled method to achieve this and also make the environment more secure, and that is to use SQL Server Agent and create a job. Here, you can provide various functionalities, such as e-mail notification of any failures, a more controlled start and end timings, as well as logging of the success or failure of the task. You can pause a stored procedure in multiples of a minute within a jobin SQL Server Agent.
Important | Be extremely careful in your use of WAITFOR(). It will stop the processing of any stored procedure containing this statement until the time is reached or until the delay as expired. Any locks in this stored procedure will obviously stop any other stored procedure until the locks in the WAITFOR() procedure are released. Although it's a useful command, you can easily put your system in trouble if you get it wrong, for example, when you get the delay calculation wrong. |