Writing Stored Procedures

Creating a Procedure

You create a stored procedure with the CREATE PROCEDURE statement (this can be abbreviated to CREATE PROC). A procedure definition consists of a procedure name, optional parameter declarations enclosed in parentheses, an optional return value type and the procedure body, which can consist of one or more SQL statements. If the procedure body contains more than one SQL statement, they must be grouped into a statement block using BEGIN/END statements.

Examples

CREATE PROC EX1 AS
BEGIN
    -- currency conversion table
    CREATE TABLE CONV (CID VARCHAR(5) PRIMARY KEY, RATE DECIMAL(5,2), CNAME VARCHAR(5))
    -- test data table
    CREATE TABLE TESTDATA (ID SMALLINT PRIMARY KEY, AMT DECIMAL(6,2))
END

Creates a procedure called EX1 with no parameters, no return value and a procedure body containing two SQL statements enclosed in BEGIN/END statements.

CREATE PROC EX5(@MIN DECIMAL(6,2) OUT, @MAX DECIMAL(6,2) OUT, @AVG DECIMAL(6,2) OUT) AS
    SELECT @MIN=MIN(AMT), @MAX=MAX(AMT), @AVG=AVG(AMT) FROM TESTDATA

Creates a procedure called EX5 that accepts three parameters, @MIN, @MAX and @AVG, all of type DECIMAL(6,2). EX5 does not return a value. The procedure body consists of a single SQL statement (which therefore does not need to be enclosed in BEGIN/END statements).

CREATE PROC EX7(@TYPE INTEGER IN) RETURNS DECIMAL(6,2) AS
BEGIN
    DECLARE @V DECIMAL(6,2)
    IF @TYPE=1
        SET @V = (SELECT MIN(AMT) FROM TESTDATA)
    ELSE IF @TYPE=2
        SET @V = (SELECT MAX(AMT) FROM TESTDATA)
    ELSE
        SET @V = (SELECT AVG(AMT) FROM TESTDATA)
    RETURN @V
END

Creates a procedure called EX7 that accepts a single parameter of type INTEGER, and returns a value of type DECIMAL(6,2). The procedure body consists of a number of SQL statements enclosed in BEGIN/END statements.

Variables

All variables used in a stored procedure must be declared, either as parameters or using the DECLARE statement, and given a data type. Once declared, they can be given values with the SET statement. The following procedure definition declares an input parameter, @TYPE, of type INTEGER, and a local variable, @V, of type DECIMAL(6,2).

CREATE PROC EX7(@TYPE INTEGER IN) RETURNS DECIMAL(6,2) AS
BEGIN
    DECLARE @V DECIMAL(6,2)
    IF @TYPE=1
        SET @V = (SELECT MIN(AMT) FROM TESTDATA)
    ELSE IF @TYPE=2
        SET @V = (SELECT MAX(AMT) FROM TESTDATA)
    ELSE
        SET @V = (SELECT AVG(AMT) FROM TESTDATA)
    RETURN @V
END

Variable names must begin with the @ character.

Parameters

Any parameters must be declared in the procedure definition and given a data type. Parameters can be of three types: INPUT, OUTPUT and INOUT - the default, if omitted, is INOUT. The values of OUTPUT and INOUT parameters can be changed using the SET statement; the new values will be returned when the procedure terminates. The values of INPUT parameters can be changed, but these changes remain local to the procedure.

Parameter names must begin with the @ character.

Returning a Value

The RETURN Statement

The RETURN statement is used to specify the return value of a stored procedure. The returned value can be the result of any valid SQL expression that evaluates to a single (scalar) value with a data type that is compatible with the declared return value of the procedure. Execution of the procedure stops when the RETURN statement is executed, even if there are other statements remaining in the procedure body.

The RETURN statement can also be used without an expression to simply terminate execution of the procedure without returning a value.

The example procedure definition above creates a procedure that returns a value of type DECIMAL(6,2). Internally, it uses a local variable, @V, the value of which is returned at the end of the procedure:

Returning a Result Set

If your procedure contains a SELECT statement that generates a result set, that result set will be returned in addition to any value specified in a RETURN statement. The effect will be the same as if the SELECT statement had been called directly and not as part of a procedure. For example:

CREATE PROC GETTESTDATA AS
    SELECT ID, AMT FROM TESTDATA ORDER BY ID

Note that each possible path through your procedure can contain only one SELECT statement that generates a result set. It can however contain other SELECT statements in which the results are assigned to variables (see SELECT Extensions below). The following example contains three SELECT statements, but only the third generates a result set:

CREATE PROC EX4(@C VARCHAR(10) IN) AS
BEGIN
    DECLARE @R DECIMAL(5,2)
    DECLARE @UKN VARCHAR(5)
    DECLARE @CNN VARCHAR(5)
    SELECT @R=RATE, @CNN=CNAME FROM CONV WHERE CID=@C
    SELECT @UKN=CNAME FROM CONV WHERE CID='UK'
    SELECT ID,AMT,@UKN 'Curr1',@R 'Rate',AMT*@R 'Converted',@CNN 'Curr2' FROM TESTDATA ORDER BY ID
END

Conditional Execution

The IF/ELSE statement allows you to execute different parts of your procedure, depending on the conditions you specify. The statement has two forms. The first is:

IF condition
statement

In this form, if the result of evaluating condition is true, statement is executed. If condition is false, statement is not executed. For example:

IF @TYPE=1
    SET @V = 'Value'

The second form of the IF statement adds an ELSE clause that is executed when condition is false:

IF condition
statement1

ELSE
statement2

In this form of the statement, if condition evaluates to true, statement1 is executed, otherwise, statement2 is executed. For example:

IF @TYPE=1
    SET @V = (SELECT MIN(AMT) FROM TESTDATA)
ELSE
    SET @V = (SELECT AVG(AMT) FROM TESTDATA)

If necessary, you can replace a single statement in the IF or ELSE clause with a statement block enclosed in BEGIN/END statements. The following example uses BEGIN/END statements to ensure that the ELSE statement is associated with the correct IF statement:

IF @I = @J
BEGIN
    IF @J = @K
        @R = "@I equals @K
END
ELSE
    @R = "@I doesn't equal @K

Looping

The WHILE statement allows you to carry out repetitive actions. It has the following syntax:

WHILE condition
statement

If condition evaluates to true, the statement that forms the body of the loop is executed and then condition is evaluated again. This is repeated until condition evaluates to false, at which point the WHILE statement ends and the procedure moves on.

As with the IF statement, the body of the loop can be a statement block enclosed in BEGIN/END statements.

Comments

SQL stored procedures support two forms of comment. Any text between a -- and the end of a line is treated as a comment and is ignored. Similarly, any text between the characters /* and */ is treated as a comment; this type of comment can span multiple lines, but cannot be nested. Comments can only occur between SQL statements.

SELECT Extensions

Within stored procedures, SQL for Reality supports extensions to the SELECT statement that allow you make use of parameters and local variables.

Assigning Results to Variables

There are several ways to assign results to variables. If your SELECT statement returns a result set containing several columns, you can assign the value of each column to a parameter or local variable. For example:

SELECT @R=RATE, @CNN=CNAME FROM CONV WHERE CID=37621

Alternatively, if the SELECT statement returns a single value, you can use the SET statement. For example:

SET @UKN = (SELECT CNAME FROM CONV WHERE CID='UK')
SET @V = (SELECT AVG(AMT) FROM TESTDATA)

Note

You can only assign results to variables if the SELECT statement returns a single row.

Using Values Held in Variables

Within a stored procedure, parameters and local variables can be used wherever you would normally use an SQL expression. This is illustrated in the example below:

Example

The following example demonstrates all these ways of using variables:

CREATE PROC EX4(@C VARCHAR(10) IN) AS
BEGIN
    DECLARE @R DECIMAL(5,2)
    DECLARE @UKN VARCHAR(5)
    DECLARE @CNN VARCHAR(5)
    SELECT @R=RATE, @CNN=CNAME FROM CONV WHERE CID=@C
    SET @UKN = SELECT CNAME FROM CONV WHERE CID='UK'
    SELECT ID,AMT,@UKN 'Curr1',@R 'Rate',AMT*@R 'Converted',@CNN 'Curr2' FROM TESTDATA ORDER BY ID
END

Using SELECT Without Specifying a Table

The SELECT statement can be used without specifying a table. For example, the following statement shows one way of assigning the current time to a variable:

SELECT @V1 = CURTIME()

The following example shows how you might create a result set from values held in variables:

DECLARE @MAX DECIMAL(6,2)
DECLARE @MIN DECIMAL(6,2)
DECLARE @AVG DECIMAL(6,2)
SET @MAX = (SELECT MIN(AMT) FROM TESTDATA)
SET @MIN = (SELECT MAX(AMT) FROM TESTDATA)
SET @AVG = (SELECT AVG(AMT) FROM TESTDATA)
SELECT @MIN 'Minimum', @MAX 'Maximum', @AVG 'Average'