CREATE PROCEDURE Statement

Creates a new stored procedure.

Syntax

CREATE [PROC || PROCEDURE] procname( {argdef{,argdef{...}}} ) {RETURNS datatype} AS statement

Syntax Elements

procname
The name of the procedure. A standard SQL identifier following the same rules as Table and Column names.

argdef =
argname datatype {=defaultvalue} {iotype}

argname
A argument name. This must obey the same rules as Table and Column names, except that it must start with an @ character.

defaultvalue =
The value used if none is specified in the procedure call.

iotype =
INPUT || OUTPUT || INOUT

Defines whether the argument is input only, output only or both. The default is INOUT.

datatype
Any supported SQL data type.

statement
Any supported SQL statement. Normally, a stored procedure will be defined as a BEGIN/END block containing multiple statements.

Comments

The following SQL statements are only valid in the statement section of a CREATE PROCEDURE statement:

BEGIN/END
BREAK
CONTINUE
DECLARE
IF/ELSE
RETURN
SET
WHILE

See SQL Stored Procedures for details of how to write and use SQL procedures.

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 that creates two tables.

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 selects the minimum, maximum and average values from the AMT column of the TESTDATA table. The results are returned in the @MIN, @MAX and @AVG arguments.

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 returns the minimum, maximum or average value from the AMT column of the TESTDATA table, depending on the value of the @TYPE parameter.

See Also

DROP PROCEDURE statement, CALL statement.