DB2 SQL Procedures

You use the CREATE PROCEDURE statement to create a procedure in a DB2^® database. The part of the CREATE PROCEDURE statement that contains the code is the procedure body.

SQL procedure definitions provide the following information:

The procedure name.
Parameter attributes.
The language in which the procedure is written. For an SQL procedure, the language is SQL.
Information about the SQL procedure that is used when the procedure is called. This information can include run-time options and whether the procedure returns result sets.

The CREATE PROCEDURE statement for an SQL procedure does not specify the EXTERNAL clause. Instead, an SQL procedure has a procedure body, which contains the source statements for the procedure.

Important: If you use Visual Studio 2008, do not create stored procedures with names that are longer than 39 characters. Stored procedures with names that are longer than 39 characters can cause errors when you use the Visual Studio 2008 debugger and other components.

The following figures show example CREATE PROCEDURE statements for simple SQL procedures. The second example shows a simple SQL procedure for z/OS. The procedure name, the list of parameters that are passed to or from the procedure, and the LANGUAGE parameter are common to all procedures. The LANGUAGE value of SQL is particular to an SQL procedure. Each line is described by number after the figure.

Figure 1. CREATE PROCEDURE statement for a simple DB2^® SQL procedure


1          CREATE PROCEDURE SCHEMA.Procedure6 ( INOUT var0 varchar(9) )
2                            LANGUAGE SQL
            --------------------------------------------------------------
3           -- SQL procedure SCHEMA.Procedure6
            --------------------------------------------------------------
4          P1: BEGIN
                   -- Declare cursor
                   DECLARE cursor1 CURSOR WITH RETURN FOR
                           SELECT * FROM STAFF;
           END P1

Line	Description
1	The SQL procedure name is `SCHEMA.Procedure6`. The INOUT parameter is named `var0` and has data type varchar(9).
2	`LANGUAGE SQL` indicates that this is an SQL procedure.
3	A comment for the SQL procedure.
4	Start of the body of the SQL procedure. All SQL procedure bodies consist of one or more statements nested between a BEGIN and an END keyword.

Figure 2. CREATE PROCEDURE statement for an SQL procedure for DB2 for z/OS^®


1          CREATE PROCEDURE SCHEMA.Proc1111 ( ) 
                   RESULT SETS 1 
2                  LANGUAGE SQL 
                   MODIFIES SQL DATA
3                  COLLID TEST
4                  NO WLM ENVIRONMENT
5                  ASUTIME NO LIMIT
                   RUN OPTIONS 'NOTEST(ALL,*,,VADTCPIP&120.1.1.1:*)'  
           ----------------------------------------------------------           
6          -- SQL Procedure
           ----------------------------------------------------------
7          P1: BEGIN    
                   -- Declare cursor
                   DECLARE cursor1 CURSOR WITH RETURN FOR 
                           SELECT SCHEMA, NAME FROM SYSIBM.SYSROUTINES;                  
                   -- Cursor left open for client application
                   OPEN cursor1;
           END P1

Line	Description
`1`	The SQL procedure name is `SCHEMA.Proc1111`.
`2`	`LANGUAGE SQL` indicates that this is an SQL procedure.
`3`	Specifies a collection ID of `TEST`.
`4`	Specifies no Workload Manager (WLM) environment.
`5`	Specifies no processor time limit for running the SQL procedure.
`6`	Shows a comment for the SQL procedure.
`7`	Begins the body of the SQL procedure. All SQL procedure bodies consist of one or more statements nested between a BEGIN and an END keyword.

See Also