Flow control


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.

Code Blocks/ BEGINEND

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.

Decisions

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.

IFELSE

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):

click to expand

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:

click to expand

By letting the cursor hover over some of the details, you can see the underlying processing.

CASE

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 

GOTO label

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.

Loops

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.

The WHILE Loop

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.

Pausing and Halting execution

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.




SQL Server 2000 Stored Procedures Handbook
SQL Server 2000 Stored Procedures Handbook (Experts Voice)
ISBN: 1590592875
EAN: 2147483647
Year: 2005
Pages: 100

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net