Some SQL statement editors — specifically the isql utility that comes with Firebird, and possibly some third-party editors — employ an internal convention that requires all statements to be terminated with a semicolon. This creates a conflict with PSQL syntax when coding in these environments. If you are unacquainted with this problem and its solution, please study the details in the PSQL chapter in the section entitled Switching the Terminator in isql.

Each parameter has a data type. The NOT NULL constraint can also be specified for any parameter, to prevent NULL being passed or assigned to it.

A collation can be specified for string-type parameters, using the COLLATE clause.

Input Parameters

Input parameters are presented as a parenthesized list following the name of the function. They are passed by value into the procedure, so any changes inside the procedure has no effect on the parameters in the caller. Input parameters may have default values. Parameters with default values specified must be added at the end of the list of parameters.

Output Parameters

The optional RETURNS clause is for specifying a parenthesised list of output parameters for the stored procedure.

The SQL SECURITY clause specifies the security context for executing other routines or inserting into other tables. When SQL Security is not specified, the default value of the database is applied at runtime.

The SQL SECURITY clause can only be specified for PSQL procedures, and is not valid for procedures defined in a package.

See also SQL Security in chapter Security.

The optional declarations section, located at the start of the body of the procedure definition, defines variables (including cursors) and subroutines local to the procedure. Local variable declarations follow the same rules as parameters regarding specification of the data type. See details in the PSQL chapter for DECLARE VARIABLE, DECLARE CURSOR, DECLARE FUNCTION, and DECLARE PROCEDURE.

A stored procedure can also be located in an external module. In this case, instead of a procedure body, the CREATE PROCEDURE specifies the location of the procedure in the external module using the EXTERNAL clause. The optional NAME clause specifies the name of the external module, the name of the procedure inside the module, and — optionally — user-defined information. The required ENGINE clause specifies the name of the UDR engine that handles communication between Firebird and the external module. The optional AS clause accepts a string literal body, which can be used by the engine or module for various purposes.

The CREATE PROCEDURE statement can be executed by:

• Administrators

• Users with the CREATE PROCEDURE privilege

The user executing the CREATE PROCEDURE statement becomes the owner of the table.

1. Creating a stored procedure that inserts a record into the BREED table and returns the code of the inserted record:

      |CREATE PROCEDURE ADD_BREED (
      |  NAME D_BREEDNAME, /* Domain attributes are inherited */
      |  NAME_EN TYPE OF D_BREEDNAME, /* Only the domain type is inherited */
      |  SHORTNAME TYPE OF COLUMN BREED.SHORTNAME,
      |    /* The table column type is inherited */
      |  REMARK VARCHAR(120) CHARACTER SET WIN1251 COLLATE PXW_CYRL,
      |  CODE_ANIMAL INT NOT NULL DEFAULT 1
      |)
      |RETURNS (
      |  CODE_BREED INT
      |)
      |AS
      |BEGIN
      |  INSERT INTO BREED (
      |    CODE_ANIMAL, NAME, NAME_EN, SHORTNAME, REMARK)
      |  VALUES (
      |    :CODE_ANIMAL, :NAME, :NAME_EN, :SHORTNAME, :REMARK)
      |  RETURNING CODE_BREED INTO CODE_BREED;
      |END
   

2. Creating a selectable stored procedure that generates data for mailing labels (from employee.fdb):

      |CREATE PROCEDURE mail_label (cust_no INTEGER)
      |RETURNS (line1 CHAR(40), line2 CHAR(40), line3 CHAR(40),
      |         line4 CHAR(40), line5 CHAR(40), line6 CHAR(40))
      |AS
      |  DECLARE VARIABLE customer VARCHAR(25);
      |  DECLARE VARIABLE first_name VARCHAR(15);
      |  DECLARE VARIABLE last_name VARCHAR(20);
      |  DECLARE VARIABLE addr1 VARCHAR(30);
      |  DECLARE VARIABLE addr2 VARCHAR(30);
      |  DECLARE VARIABLE city VARCHAR(25);
      |  DECLARE VARIABLE state VARCHAR(15);
      |  DECLARE VARIABLE country VARCHAR(15);
      |  DECLARE VARIABLE postcode VARCHAR(12);
      |  DECLARE VARIABLE cnt INTEGER;
      |BEGIN
      |  line1 = '';
      |  line2 = '';
      |  line3 = '';
      |  line4 = '';
      |  line5 = '';
      |  line6 = '';
      | 
      |  SELECT customer, contact_first, contact_last, address_line1,
      |    address_line2, city, state_province, country, postal_code
      |  FROM CUSTOMER
      |  WHERE cust_no = :cust_no
      |  INTO :customer, :first_name, :last_name, :addr1, :addr2,
      |    :city, :state, :country, :postcode;
      | 
      |  IF (customer IS NOT NULL) THEN
      |    line1 = customer;
      |  IF (first_name IS NOT NULL) THEN
      |    line2 = first_name || ' ' || last_name;
      |  ELSE
      |    line2 = last_name;
      |  IF (addr1 IS NOT NULL) THEN
      |    line3 = addr1;
      |  IF (addr2 IS NOT NULL) THEN
      |    line4 = addr2;
      | 
      |  IF (country = 'USA') THEN
      |  BEGIN
      |    IF (city IS NOT NULL) THEN
      |  	  line5 = city || ', ' || state || '  ' || postcode;
      |  	ELSE
      |      line5 = state || '  ' || postcode;
      |  END
      |  ELSE
      |  BEGIN
      |    IF (city IS NOT NULL) THEN
      |  	  line5 = city || ', ' || state;
      |  	ELSE
      |      line5 = state;
      |    line6 = country || '    ' || postcode;
      |  END
      | 
      |  SUSPEND; -- the statement that sends an output row to the buffer
      |           -- and makes the procedure "selectable"
      |END
   

3. With DEFINER set for procedure p, user US needs only the EXECUTE privilege on p. If it were set for INVOKER, either the user or the procedure would also need the INSERT privilege on table t.

      |set term ^;
      |create procedure p (i integer) SQL SECURITY DEFINER
      |as
      |begin
      |  insert into t values (:i);
      |end^
      |set term ;^
      | 
      |grant execute on procedure p to user us;
      |commit;
      | 
      |connect 'localhost:/tmp/17.fdb' user us password 'pas';
      |execute procedure p(1);
   

See alsoSection 5.8.3, “CREATE OR ALTER PROCEDURE”, Section 5.8.2, “ALTER PROCEDURE”, Section 5.8.5, “RECREATE PROCEDURE”, Section 5.8.4, “DROP PROCEDURE”

The ALTER PROCEDURE statement can be executed by:

• Administrators

• The owner of the stored procedure

• Users with the ALTER ANY PROCEDURE privilege

Altering the GET_EMP_PROJ stored procedure.

   |ALTER PROCEDURE GET_EMP_PROJ (
   |  EMP_NO SMALLINT)
   |RETURNS (
   |  PROJ_ID VARCHAR(20))
   |AS
   |BEGIN
   |  FOR SELECT
   |      PROJ_ID
   |    FROM
   |      EMPLOYEE_PROJECT
   |    WHERE
   |      EMP_NO = :emp_no
   |    INTO :proj_id
   |  DO
   |    SUSPEND;
   |END

See alsoSection 5.8.1, “CREATE PROCEDURE”, Section 5.8.3, “CREATE OR ALTER PROCEDURE”, Section 5.8.5, “RECREATE PROCEDURE”, Section 5.8.4, “DROP PROCEDURE”

Creating or altering the GET_EMP_PROJ procedure.

   |CREATE OR ALTER PROCEDURE GET_EMP_PROJ (
   |    EMP_NO SMALLINT)
   |RETURNS (
   |    PROJ_ID VARCHAR(20))
   |AS
   |BEGIN
   |  FOR SELECT
   |      PROJ_ID
   |    FROM
   |      EMPLOYEE_PROJECT
   |    WHERE
   |      EMP_NO = :emp_no
   |    INTO :proj_id
   |  DO
   |    SUSPEND;
   |END

See alsoSection 5.8.1, “CREATE PROCEDURE”, Section 5.8.2, “ALTER PROCEDURE”, Section 5.8.5, “RECREATE PROCEDURE”

The DROP PROCEDURE statement can be executed by:

• Administrators

• The owner of the stored procedure

• Users with the DROP ANY PROCEDURE privilege

Deleting the GET_EMP_PROJ stored procedure.

  |DROP PROCEDURE GET_EMP_PROJ;

See alsoSection 5.8.1, “CREATE PROCEDURE”, Section 5.8.5, “RECREATE PROCEDURE”

Creating the new GET_EMP_PROJ stored procedure or recreating the existing GET_EMP_PROJ stored procedure.

   |RECREATE PROCEDURE GET_EMP_PROJ (
   |  EMP_NO SMALLINT)
   |RETURNS (
   |  PROJ_ID VARCHAR(20))
   |AS
   |BEGIN
   |  FOR SELECT
   |      PROJ_ID
   |    FROM
   |      EMPLOYEE_PROJECT
   |    WHERE
   |      EMP_NO = :emp_no
   |    INTO :proj_id
   |  DO
   |    SUSPEND;
   |END

See alsoSection 5.8.1, “CREATE PROCEDURE”, Section 5.8.4, “DROP PROCEDURE”, Section 5.8.3, “CREATE OR ALTER PROCEDURE”