CREATE PROCEDURE Statement

Creates a new user-defined SQL procedure in the database.

To create external procedure interfaces, see CREATE PROCEDURE Statement (External Procedures).

Quick Links:

Go to Parameters

Go to Examples

Go to Usage

Go to Standards

Go to Permissions

Syntax

CREATEOR REPLACE | TEMPORARY ] PROCEDUREowner.]procedure-name ( [ parameter, …] ) { 
   [ SQL SECURITY { INVOKER | DEFINER } ]
   [ RESULTresult-column, …) | NO RESULT SET ]  
   [ ON EXCEPTION RESUME ] compound statement | AT location-string

parameter - (back to Syntax)
   parameter_mode parameter-name data-typeDEFAULT expression ] | SQLCODE | SQLSTATE

parameter_mode - (back to parameter)
   IN | OUT | INOUT

result-column - (back to Syntax)
   column-name data-type

Parameters

(back to top)

Examples

(back to top)

Usage

(back to top)

CREATE PROCEDURE creates a procedure in the database. A procedure is invoked with a CALL statement. You can create permanent or temporary (TEMPORARY) stored procedures. You can use PROC as a synonym for PROCEDURE.

Note: There are two ways to create stored procedures: ISO/ANSI SQL and T-SQL. BEGIN TRANSACTION, for example, is T-SQL-specific when using CREATE PROCEDURE syntax. Do not mix syntax when creating stored procedures. See CREATE PROCEDURE Statement [T-SQL].

When procedures are executed using CALL, not all parameters need to be specified. If a default value is provided in the CREATE PROCEDURE statement, missing parameters are assigned the default values. If an argument is not provided in the CALL statement, and no default is set, an error is given.

Remote procedures can return only up to 254 characters in output variables.

If a remote procedure can return a result set, even if it does not return one in all cases, then the local procedure definition must contain a RESULT clause.

For information on remote servers, see CREATE SERVER Statement.

Side Effects
  • Automatic commit

Standards

(back to top)

  • SQL—ISO/ANSI SQL compliant.
  • SAP Sybase Database product—The Transact-SQL CREATE PROCEDURE statement is different.
  • SQLJ—The syntax extensions for Java result sets are as specified in the proposed SQLJ1 standard.

Permissions

(back to top)

Watcom SQL or Transact SQL procedure to be owned by self – Requires CREATE PROCEDURE system privilege.

Watcom SQL or Transact SQL procedure to be owned by any user – Requires one of:
  • CREATE ANY PROCEDURE system privilege.
  • CREATE ANY OBJECT system privilege.
Remote procedure to be owned by self – Requires all of:
  • CREATE EXTERNAL REFERENCE system privilege.
  • CREATE PROCEDURE system privilege.
Remote procedure to be owned by any user – Requires CREATE EXTERNAL REFERENCE system privilege. Also requires one of:
  • CREATE ANY PROCEDURE system privilege.
  • CREATE ANY OBJECT system privilege.
Related concepts
Referencing Temporary Tables Within Procedures
Related reference
BEGIN … END Statement
CALL Statement
CREATE PROCEDURE Statement [T-SQL]
CREATE PROCEDURE Statement (External Procedures)
CREATE SERVER Statement
DROP Statement
EXECUTE IMMEDIATE Statement [ESQL] [SP]
GRANT EXECUTE Statement
RAISERROR Statement [T-SQL]
ON_TSQL_ERROR Option [TSQL]