Conditional Execution

In some cases, you might only want a stored procedure to execute a query if certain conditions are met, such as valid parameters being supplied. Alternatively, you might execute one query and then only move on to execute another if the result of the first query meets your approval. Also, you might have several statements that you could execute and choose one of them based on a parameter or query result.

Two types of conditional statements are available in most RDBMSs:

• IF...ELSE statements, where one of two blocks of code is executed depending on a condition

• CASE...WHEN statements, where a value is used to choose the result of the structure, which may take one of several values

The SQL Server structure for IF...ELSE is as follows:

IF Condition
BEGIN
   ...Statements executed if Condition evaluates to true
END
ELSE
BEGIN
   ...Statements executed if Condition evaluates to false
END

The BEGIN and END keywords can be omitted if only a single statement is required in a block, and the whole ELSE block can be omitted if desired.

Oracle and DB2 require a THEN keyword after each condition and also allow the use of ELSIF/ELSEIF clauses so that you can evaluate more than one condition (in SQL Server, you just use a nested IF statement instead). The syntax for Oracle is as follows:

IF Condition THEN
   ...Statements if Condition evaluates to true ...
ELSIF Condition2 THEN
   ...Statements if Condition evaluates to false
       and Condition2 evaluates to true...
ELSE
   ...Statements if Condition and Condition2
       both evaluate to false...
END IF;

Here, ELSIF and ELSE blocks are optional. DB2 is identical except that it uses two Es in ELSEIF.

In all cases, the conditions are simply Boolean expressions of the type you've used many times before.

You can also use CASE...WHEN statements to test a single expression for multiple values:

CASE Expression
   WHEN Value1 THEN Result1
   WHEN Value2 THEN Result2
   ...
   WHEN ValueN THEN ResultN
   ELSE ResultElse
END;

Here, Expression is evaluated, and if it matches any of the values listed, then the result of the structure is the result associated with the first matching value. Any subsequent matches are ignored. If no match is made, then the result is ResultElse (if this appears because the ELSE section is optional). In SQL Server, CASE can only be used within a SELECT or variable assignment statement:

SET @somevar = CASE @someothervar
                   WHEN 1 THEN 'one'
                   WHEN 2 THEN 'two'
                   ELSE 'can''t count that high'
                END;

In Oracle and DB2, CASE can also be used as in procedural programming languages—the result for each WHEN case can be a complete statement. Used in this way, you need to provide semicolons at the end of each statement, and the CASE statement is terminated by END CASE rather than just END. For example, in DB2:

CASE somevar
   WHEN 1 THEN SET someothervar = 'one';
   WHEN 2 THEN SET someothervar = 'two';
   ELSE SET someothervar = 'can''t count that high';
END CASE;

The Oracle syntax is identical (except, of course, that the syntax for the variable assignations uses := rather than SET).

You can also look at several conditions in a CASE statement:

CASE
   WHEN Comparison1 THEN Result1
   WHEN Comparison2 THEN Result2
   ...
   WHEN ComparisonN THEN ResultN
   ELSE ResultElse
END;

If any of the comparisons evaluate to true, then the result is determined by the THEN clause for the first match (subsequent matches will be ignored). As before, if none is true, then the result is ResultElse.

Conditional Execution Example

Let's look at an example of using conditions in stored procedures. You'll create a stored procedure that calculates the average mark that a particular student scored in his or her exams and then returns a comment about how that student is doing based on this average. The ID will be passed in as an input parameter, and the comment will be returned as an output parameter. The code is similar for all three systems, but you'll look at each one individually because the syntax for assigning variables and so on is different.

SQL Server

The chief point to notice about the SQL Server version of this procedure is the slightly divergent syntax for the IF and CASE constructions.

CONDITIONAL EXPRESSIONS (SQL SERVER)

Enter this code into Query Analyzer and execute it:

CREATE PROCEDURE GetStudentComments (
          @i_StudentID INT, @o_Comments VARCHAR(100) OUTPUT)
AS
BEGIN
DECLARE @exams_sat    INT;
DECLARE @avg_mark     INT;
DECLARE @tmp_comments VARCHAR(100);
SET @exams_sat = (SELECT COUNT(ExamID) FROM StudentExam
                    WHERE StudentID = @i_StudentID);
IF @exams_sat = 0
   SET @avg_mark = -1;
ELSE
   SET @avg_mark = (SELECT AVG(Mark) FROM StudentExam
                     WHERE StudentID = @i_StudentID);
   SET @tmp_comments = CASE
      WHEN @avg_mark < 0 THEN
         'n/a – this student sat no exams'
      WHEN @avg_mark < 50 THEN
         'Very poor. Needs to spend less time in the bar.'
      WHEN @avg_mark < 60 THEN
         'Adequate, but could work harder.'
      WHEN @avg_mark < 70 THEN
         'Very satisfactory. Should pass easily.'
      ELSE 'Excellent! Will pass with flying colors.'
   END;
SET @o_Comments = @tmp_comments;
END;

You start by declaring three variables: a variable to hold the number of the exams sat by the student, a variable to hold the average mark in the exams, and a variable to hold the comments to which you'll eventually assign the output parameter. Next, you retrieve the number of exams taken by the student into a variable called @exams_sat. If this is zero, you set the @avg_mark variable to -1. Otherwise, you set this variable to the average mark for the student.

Once you've set @avg_mark, you use a CASE statement to set the @tmp_comments variable. If @avg_mark is less than zero, you return a message indicating that you have no comments because the student hasn't taken any exams. Otherwise, you set the message to reflect the marks scored by the student. Finally, you set the output parameter to the value of @tmp_comments.

Once you've created the procedure, you can run it from Query Analyzer as follows:

DECLARE @comments VARCHAR(100);
EXEC GetStudentComments 12, @comments OUTPUT;
PRINT @comments;

The result of this is as follows:

   n/a - this student sat no exams

Oracle

The Oracle code for this procedure is similar to the SQL Server version, but there are some differences in the way the conditional statements are implemented.

CONDITIONAL EXPRESSIONS (ORACLE)

Enter this code into SQL*Plus:

CREATE OR REPLACE PROCEDURE GetStudentComments(
                                      i_StudentID IN  INT,
                                      o_Comments  OUT VARCHAR)
AS
   exams_sat    INT;
   avg_mark     INT;
   tmp_comments VARCHAR(100);
BEGIN
SELECT COUNT(ExamID) INTO exams_sat FROM StudentExam
   WHERE StudentID = i_StudentID;
IF exams_sat = 0 THEN
   tmp_comments := 'n/a - this student sat no exams';
ELSE
   SELECT AVG(Mark) INTO avg_mark FROM StudentExam
   WHERE StudentID = i_StudentID;
   CASE
      WHEN avg_mark < 50 THEN tmp_comments :=
         'Very poor. Needs to spend less time in the bar.';
      WHEN avg_mark < 60 THEN tmp_comments :=
         'Adequate, but could work harder.';
      WHEN avg_mark < 70 THEN tmp_comments :=
         'Very satisfactory. Should pass easily.';
      ELSE tmp_comments :=
         'Excellent! Will pass with flying colors.';
   END CASE;
END IF;
o_Comments := tmp_comments;
END;
/

The first thing to notice is that you place the variable declarations between the AS keyword and the BEGIN keyword that marks the start of the procedure body. When you declare local variables within a procedure, notice that you don't include the DECLARE keyword.

Within the procedure body, you store the number of exams taken by the student in the exams_sat variable using Oracle's SELECT INTO syntax. If this is zero, you set the message in the tmp_comments variable to indicate that comments aren't applicable. Otherwise, you retrieve the average exam mark for the student and use a CASE statement to set tmp_comments. Notice that you use the alternative syntax for CASE here, whereby the result for each case is a complete statement, rather than a value that you store in a variable. Finally, you set the output parameter to return the value stored in tmp_comments.

You can run this procedure from SQL*Plus using the following code:

SET SERVEROUT ON
DECLARE
   comments VARCHAR(100);
BEGIN
   GetStudentComments(2, comments);
   dbms_output.put_line(comments);
END;
/

The output from this is as follows:

   Very poor. Needs to spend less time in the bar.

DB2

The DB2 code for this procedure is fundamentally similar to that for Oracle. Apart from the spelling of ELSEIF, the syntax for IF and CASE statements is identical in DB2 and Oracle.

CONDITIONAL EXPRESSIONS (DB2)

Create and build the following stored procedure using Development Center:

CREATE PROCEDURE DB2ADMIN.GetStudentComments (
            IN i_StudentID INT, OUT o_Comments VARCHAR(100))
P1: BEGIN
DECLARE exams_sat    INT;
DECLARE avg_mark     INT;
DECLARE tmp_comments VARCHAR(100);
SET exams_sat = (SELECT COUNT(ExamID) FROM StudentExam
                  WHERE StudentID = i_StudentID);
IF exams_sat = 0 THEN
   SET tmp_comments = 'n/a - this student sat no exams';
ELSE
   SET avg_mark = (SELECT AVG(Mark) FROM StudentExam
                    WHERE StudentID = i_StudentID);
   CASE
      WHEN avg_mark < 50 THEN SET tmp_comments =
         'Very poor. Needs to spend less time in the bar.';
      WHEN avg_mark < 60 THEN SET tmp_comments =
         'Adequate, but could work harder.';
      WHEN avg_mark < 70 THEN SET tmp_comments =
         'Very satisfactory. Should pass easily.';
      ELSE SET tmp_comments =
         'Excellent! Will pass with flying colors';
   END CASE;
END IF;
SET o_Comments = tmp_comments;
END P1

Again, you start by declaring the variables you'll be using in the procedure. You then check whether the student took any exams and if so store the average mark; otherwise, you set tmp_comments to indicate that comments aren't applicable. If exams were set, you use the average mark value in a CASE statement to set the tmp_comments variable to the message that indicates how the student is performing. Finally, you return this message from the procedure by assigning the value of the output parameter to tmp_comments.

You can run this procedure from Command Center as follows:

CALL GetStudentComments(3, ?)

This gives the following output:

   Value of output parameters

   --------------------------
   Parameter Name  : O_COMMENTS
   Parameter Value : Excellent! Will pass with flying colors.

   Return Status = 0

Loops

The various dialects of SQL each have their own ways of performing loops, that is, repeatedly executing blocks of SQL statements. SQL Server, DB2, and Oracle all support the WHILE loop. Unfortunately, the syntax is slightly different in each case.

In SQL Server, WHILE is followed by either a single statement or a BEGIN...END block of SQL statements:

WHILE Condition
BEGIN
   ...statements to execute if Condition is true...
END;

With Oracle, you mark the start of the loop using the LOOP keyword placed after the condition and mark the end of the block using END LOOP:

WHILE Condition
LOOP
   ...statements to execute if Condition is true...
END LOOP;

DB2 uses DO to mark the start of the loop and END WHILE to mark the end:

WHILE Condition
DO
   ...statements to execute if Condition is true...
END WHILE;

In each case, the block of statements (or a single statement) executes if Condition is true and again if Condition is still true and so on. If, at the end of the block of statements, Condition evaluates to false, then the loop ends and processing continues. If Condition is false to start off with, then the block never executes.

This is the most basic type of loop, but some RDBMSs support variations of this. Oracle and DB2 also support FOR loops, which are used to iterate through every row in a cursor. Because loops are particularly important with cursors, you'll look at examples of looping shortly.