Defining a procedure with default parameters

External and SQL procedures can be created with optional parameters. Optional procedure parameters are defined to have a default value.

Suppose you have a procedure that is defined with parameters as follows. Whether this is part of an SQL procedure or an external procedure statement doesn’t matter; only the parameter definitions are being discussed here.

       CREATE PROCEDURE UPDATE_EMPLOYEE_INFO
         (IN EMPLOYEE_NUMBER CHAR(10),
          IN EMP_DEPT CHAR(3),
          IN PHONE_NUMBER CHAR(4))
          . . .

This procedure has been in use for a long time and is invoked from many places. Now, someone has suggested that it would be useful to have this procedure update a few other columns in the same table, JOB and EDLEVEL. Finding and changing all the calls to this procedure is a huge job, but if you add the new parameters so they have default values it is very easy.

The parameter definitions in the following CREATE PROCEDURE statement will allow all of the columns except the employee number to be passed in optionally.

       CREATE OR REPLACE PROCEDURE UPDATE_EMPLOYEE_INFO
         (IN EMPLOYEE_NUMBER CHAR(10),
          IN EMP_DEPT CHAR(3) DEFAULT NULL,
          IN PHONE_NUMBER CHAR(4) DEFAULT NULL,
          IN JOB CHAR(8) DEFAULT NULL,
          IN EDLEVEL SMALLINT DEFAULT NULL)
          . . .

The code for this procedure, either an SQL routine or an external program, needs to be modified to handle the new parameters and to correctly process the two existing parameters when a NULL value is passed. Since default parameters are optional, any existing call to this procedure will not need to change; the two new parameters will pass a value of NULL to the procedure code. Any caller who needs the new parameters can include them on the SQL CALL statement.

Although this example uses NULL for all the default values, almost any expression can be used. It can be a simple constant or a complex query. It cannot reference any of the other parameters.

There are several ways to have the defaults used for the CALL statement.

Omit the parameters at the end that you do not need to use.
```
CALL UPDATE_EMPLOYEE_INFO('123456', 'D11', '4424')
```
The defaults will be used for the JOB and EDLEVEL parameters.

Use the DEFAULT keyword for any parameters that are omitted.
```
CALL UPDATE_EMPLOYEE_INFO('123456', DEFAULT, '4424', DEFAULT, DEFAULT)
```
All the parameters are represented in this statement. The defaults will be used for the EMP_DEPT, JOB, and EDLEVEL parameters.

Explicitly name some of the arguments with the corresponding parameter name and omit parameters that are not used.
```
CALL UPDATE_EMPLOYEE_INFO('123456', EDLEVEL => 18)
```
By using the parameter name, the other three parameters do not need to be represented in this CALL statement. The defaults will be used for the EMP_DEPT, PHONE_NUMBER, and JOB parameters.

Named arguments can be in any order in the CALL statement. Unnamed arguments must match the order of the parameter definitions for the procedure and must be specified ahead of any named arguments. Once a named argument is used in the statement, all arguments that follow it must also be named. Any parameters that do not have an argument in the CALL statement must have a default defined.

In the following example, the procedure creates a table containing all employees in a specified department. The schema where it gets created has always been hard-coded for the environment where it is used. For testing, however, it would be convenient to create the table in a test schema.

CREATE OR REPLACE PROCEDURE CREATE_DEPT_TABLE2 
                   (IN P_DEPT CHAR(3),
                    IN SCHEMA_NAME VARCHAR(128) DEFAULT 'CORPDATA')
   BEGIN
     DECLARE DYNAMIC_STMT VARCHAR(1000);
     DECLARE MESSAGE CHAR(20);
     DECLARE TABLE_NAME VARCHAR(200);
     DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    
     SET MESSAGE = 'ok';
     SET TABLE_NAME = '"' CONCAT SCHEMA_NAME CONCAT
           '".DEPT_' CONCAT P_DEPT CONCAT '_T';

     SET DYNAMIC_STMT = 'DROP TABLE ' CONCAT TABLE_NAME;
     EXECUTE IMMEDIATE DYNAMIC_STMT;

     SET DYNAMIC_STMT = 'CREATE TABLE ' CONCAT TABLE_NAME CONCAT
       '( EMPNO CHAR(6) NOT NULL,
          FIRSTNME VARCHAR(12) NOT NULL,
          MIDINIT CHAR(1) NOT NULL,
          LASTNAME CHAR(15) NOT NULL,
          SALARY DECIMAL(9,2))';
     EXECUTE IMMEDIATE DYNAMIC_STMT;

     SET DYNAMIC_STMT = 'INSERT INTO ' CONCAT TABLE_NAME CONCAT
       'SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, SALARY
         FROM CORPDATA.EMPLOYEE
         WHERE  WORKDEPT = ?';
     PREPARE INSERT_INTO_DEPARTMENT_STMT FROM DYNAMIC_STMT;
     EXECUTE INSERT_INTO_DEPARTMENT_STMT USING P_DEPT;
END;

A second parameter is defined to pass a schema name. It has a default of ’CORPDATA’. This is the value that has been used by the procedure in the past.

When run in the production environment, the CALL statement might be:

CALL CREATE_DEPT_TABLE2('D21')

This creates the table in the schema provided by the default, CORPDATA.

When run in the test environment, the CALL statement might be:

CALL CREATE_DEPT_TABLE2('D21', 'TESTSCHEMA')

This creates the table in the specified schema, TESTSCHEMA.