CFPROCPARAM

The CFPROCPARAM tag is nested within a CFSTOREDPROC tag. You use it to specify parameter information, including type, name, value, and length.

Syntax

<CFPROCPARAM TYPE="IN/OUT/INOUT"
    VARIABLE="variable name"
    DBVARNAME="DB variable name"
    VALUE="parameter value"
    CFSQLTYPE="parameter datatype"
    MAXLENGTH="length"
    SCALE="decimal places" 
    NULL="Yes" or "No">

TYPE

Optional. Indicates whether the passed variable is an input, output or input/output variable. Default is IN.

VARIABLE

Required for OUT and INOUT parameters. This is the ColdFusion variable name that you use to reference the value that the output parameter represents after the call is made to the stored procedure.

DBVARNAME

Required if named notation is desired. This is the parameter name. This corresponds to the name of the parameter in the stored procedure.

VALUE

Required for IN and INOUT parameters. This corresponds to the actual value that ColdFusion passes to the stored procedure.

CFSQLTYPE

Required. This is the SQL type that the parameter (any type) will be bound to. The CFSQLTypes are as follows:


CF_SQL_BIGINT	CF_SQL_IDSTAMP	CF_SQL_REFCURSOR
CF_SQL_BIT	CF_SQL_INTEGER	CF_SQL_SMALLINT
CF_SQL_CHAR	CF_SQL_LONGVARCHAR	CF_SQL_TIME
CF_SQL_DATE	CF_SQL_MONEY	CF_SQL_TIMESTAMP
CF_SQL_DECIMAL	CF_SQL_MONEY4	CF_SQL_TINYINT
CF_SQL_DOUBLE	CF_SQL_NUMERIC	CF_SQL_VARCHAR
CF_SQL_FLOAT	CF_SQL_REAL

MAXLENGTH

Optional. Maximum length of the parameter.

SCALE

Optional. Number of decimal places of the parameter.

NULL

Optional. Specify Yes or No. Indicates whether the parameter is passed as a NULL. If you specify Yes, the tag ignores the VALUE attribute.

Usage

Use this tag to identify stored procedure parameters and their data types. Code one CFPROCPARAM tag for each parameter. The parameters you code vary, based on parameter type and DBMS. Additionally, the order in which you code CFPROCPARAM tags matters, depending on whether the stored procedure was coded using positional notation or named notation:

Positional notation -- Order is very important if the stored procedure was defined using positional notation. ColdFusion passes these parameters to the stored procedure in the order in which they are defined.
Named notation -- If named notation is used, the DBVarName for the parameter must correspond to the variable name in the stored procedure on the server.

Output variables will be scoped with the name of the VARIABLE attribute that was passed to the tag.

In 4.5 and above, CFML supports Oracle 8's REFERENCE CURSOR type. A REFERENCE CURSOR allows you to pass a parameter by reference. Therefore, parameters that are passed by reference can by allocated and deallocated memory within the course of one application. See the example for an illustration of the use of REFERENCE CURSORS.

Example

This example shows an Oracle 8 PL/SQL stored procedure, and the CFML code used to invoke it. In particular, it makes use of Oracle 8's support of the REFERENCE CURSOR type.

The following package Foo_Data houses a procedure refcurproc that declares two output parameters as REFERENCE CURSORS. The first parameter pParam1 returns all of the rows in the EMP table, and the second parameter pParam2 returns all of the rows in the DEPT table. In addition the procedure declares one input parameter as an integer, and one output parameter as a two byte char varying type. Before this procedure can be called by CFSTOREDPROC, it must be created, compiled and bound in the RDBMS environment.

CREATE OR REPLACE PACKAGE Foo_Data AS

     TYPE EmpTyp IS REF CURSOR RETURN Emp%ROWTYPE;
     TYPE DeptTyp IS REF CURSOR RETURN Dept%ROWTYPE;
 PROCEDURE refcurproc(pParam1 in out EmpTyp, pParam2 in out DeptTyp, 
pParam3 in integer, pParam4 out varchar2);
END foo_data;

CREATE OR REPLACE PACKAGE BODY Foo_Data AS
    PROCEDURE RefCurProc(pParam1 in out EmpTyp,
             pParam2 in out DeptTyp,
             pParam3 in integer,
             pParam4 out varchar2) IS
    BEGIN
           OPEN pParam1 FOR select * from emp;
           OPEN pParam2 FOR select * from dept;
           IF pParam3 = 1
           THEN
           pParam4 := 'hello';
           ELSE
           pParam4 := 'goodbye';
           END IF;
    END RefCurProc;
END Foo_Data;

The following CFML example shows how to invoke the RefCurProc procedure using CFSTOREDPROC, CFPROCPARAM, and CFPROCRESULT.

<CFSTOREDPROC    PROCEDURE="foo_data.refcurproc"
                DATASOURCE="oracle8i" 
                USERNAME = "scott"
                PASSWORD = "tiger"
                RETURNCODE="No"
>

    <CFPROCPARAM type="Out" CFSQLTYPE="CF_SQL_REFCURSOR" 
        VARIABLE="param1">
    <CFPROCPARAM type="Out" CFSQLTYPE="CF_SQL_REFCURSOR"
        VARIABLE="param2">
    <CFPROCPARAM TYPE = "IN" CFSQLTYPE ="CF_SQL_INTEGER" VALUE = "1">

    <CFPROCPARAM TYPE="OUT" CFSQLTYPE="CF_SQL_VARCHAR" VARIABLE="FOO">

    <CFPROCRESULT NAME="rs1">
    <CFPROCRESULT NAME="rs2" RESULTSET="2">

</CFSTOREDPROC>

<b>The first result set:</b><br>
<hr>
<CFTABLE QUERY ="rs1" COLHEADERS HTMLTABLE BORDER ="1">
    <CFCOL HEADER = "EMPNO" TEXT = "#EMPNO#">
    <CFCOL HEADER = "EMPLOYEE NAME" TEXT = "#ENAME#">
    <CFCOL HEADER = "JOB" TEXT = "#JOB#">
    <CFCOL HEADER = "SALARY" TEXT = "#SAL#">
    <CFCOL HEADER = "DEPT NUMBER" TEXT = "#DEPTNO#">
</CFTABLE>

<hr>
<b>The second result set:</b><br>

<CFTABLE QUERY = "rs2" COLHEADERS HTMLTABLE BORDER = "1">
    <CFCOL HEADER = "DEPT NAME" TEXT = "#DNAME#">
    <CFCOL HEADER = "DEPT NUMBER" TEXT = "#DEPTNO#">
</CFTABLE>
<hr>
<CFOUTPUT>
<b>The output parameter is:</b>'#FOO#'
</CFOUTPUT>

CFML Language Reference
Chapter 1 : ColdFusion Tags