Conditional Statements in Sybase SQL Anywhere

Handling Sybase Events with Triggers and Procedures Using SQL – Part 11

Division 5

Forward: In this part of the series, we look at conditional SQL statements in Sybase SQL Anywhere 12.

By: Chrysanthus Date Published: 28 Aug 2012

Introduction

This is part 11 of my series, Handling Sybase Events with Triggers and Stored Procedures Using SQL. This series is the fifth division of my larger series, Database. You must have read all the different parts of the big series (or equivalent tutorials from elsewhere) up to this point. In this part of the series, we look at conditional SQL statements in Sybase SQL Anywhere 12.

Note: If you cannot see the code or if you think anything is missing (broken link, image absent), just contact me at forchatrans@yahoo.com. That is, contact me for the slightest problem you have about what you are reading.

Illustration
If you do not eat then you would die of hunger. This conditional statement starts with the important, word, IF. After that you have the condition, which is the phrase, “you do not eat”. After, you have the important word, THEN. After you have what happens, which is “you would die of hunger”. You can have more than one thing that would happen, as in: If you do not eat, then you would die and you would be buried. In this second conditional statement, two things happen, which are, “you would die of hunger” and “you would be buried”.

There is an IF SQL statement, which does a very similar thing to the language conditional.

Basic Form of IF Statement
The syntax of the basic form of the IF statement is:

IF search-condition THEN
-- SQL statements
END IF;

So you have the IF reserved word, after you have the reserved word, THEN. After you have at least one SQL statement that must be executed if the search-condition is true. After you have the phrase, “END IF;”, that marks the end of the IF statement. If the search-condition is false, then no SQL statement between THEN and END IF will be executed. The group of statements between THEN and END IF is called a block. Note the semicolon at the end of END IF

Example
Try this code:

CREATE VARIABLE myVar INT = 25;

IF myVar = 25 THEN
    SELECT * FROM Invoice;
END IF;

The above code (IF block) should be executed. The following IF block (between THEN and END IF) will not be executed because the search-condition is false, try the code:

CREATE VARIABLE hisVar INT = 10;

IF hisVar = 25 THEN
    SELECT * FROM Invoice;
END IF;

IF … THEN … ELSE
You have another situation where if the search-condition is true, then one block of SQL statements should be executed and if the search-condition is not true, a different block will be executed. The syntax is:

IF search-condition THEN
    -- Execute this block is true
ELSE
    -- Execute this block if false
END IF;

So if the condition is true, the first block is executed. If the condition is false the second block is executed. The ELSE reserved word makes it possible for the alternative (second) block to be executed if the search condition is false. The long IF statement here ends with “END IF” and a semicolon.

Try the following code, in which the alternative block is executed:

CREATE VARIABLE theVar INT = 10;

IF theVar = 25 THEN
    SELECT * FROM Invoice;
ELSE
    SELECT * FROM InvoiceDetails;
END IF;

One Block from a SET of Blocks
You may have many blocks where each block has its own search-condition and you expect only one block to be executed. In that case you have to use the following structure:

IF search-condition1 THEN
    -- Block 1
ELSEIF search-condition2 THEN
    -- Block 2
ELSEIF search-condition3 THEN
    -- Block 3
    - - -
ELSE
    -- Default Block
END IF;

In this structure, only one search-condition and its block can turn out to be true. Note the use of the reserved word, ELSEIF. The ELSE (last) block here does not have any search-condition. This block is not obligatory, but you can use it as the default block in case none of the blocks above is executed. If the ELSE block is there, then it will be executed if none of the blocks above is executed; that is, it will be executed if none of the search-conditions above is true. If the ELSE block is not included and none of the above search-condition is true, no block will be executed.

Read and try the following example:

CREATE VARIABLE yourVar INT = 100;

IF yourVar = 10 THEN
    SELECT * FROM Sales;
ELSEIF yourVar = 20 THEN
    SELECT * FROM Orders;
ELSEIF yourVar = 30 THEN
    SELECT * FROM Employee;
ELSE
    SELECT * FROM Products;
ENDIF;

That is it for this part of the series. Let us stop here and continue in the next part of the series.

Chrys

Broad Network

Related Articles

Conditional Statements in Sybase SQL Anywhere

Handling Sybase Events with Triggers and Procedures Using SQL – Part 11

Division 5

Introduction

Related Courses

Comments