CFML Reference
ColdFusion Tags

cfprocparam

Description

Specifies parameter information, including type, name, value, and length. The cfprocparam tag is nested within a cfstoredproc tag.

Category

Database manipulation tags

Syntax

<cfprocparam type = "IN or OUT or INOUT"

  variable = "variable name"

  dbVarName = "DB variable name"

  value = "parameter value"

  CFSQLType = "parameter datatype"

  maxLength = "length"

  scale = "decimal places" 

  null = "Yes" or "No"> 

See also

cfinsert, cfprocresult, cfquery, cfqueryparam, cfstoredproc, cftransaction, cfupdate

Attributes

Attribute	Description
type	Optional. Indicates whether the passed variable is an input, output or input/output variable. Default is IN. The value IN passes the parameter by value. Values OUT and INOUT pass parameters as bound variables.
variable	Required for OUT and INOUT parameters. 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 for named notation. The parameter name corresponding to the name of the parameter in the stored procedure.
value	Required for IN and INOUT parameters. Corresponds to the actual value that ColdFusion passes to the stored procedure.
CFSQLType	Required. The SQL type that the parameter (any type) is bound to. The CFSQLTypes are as follows: CF_SQL_BIGINT CF_SQL_BIT CF_SQL_CHAR CF_SQL_DATE CF_SQL_DECIMAL CF_SQL_DOUBLE CF_SQL_FLOAT CF_SQL_IDSTAMP CF_SQL_INTEGER CF_SQL_LONGVARCHAR CF_SQL_MONEY CF_SQL_MONEY4 CF_SQL_NUMERIC CF_SQL_REAL CF_SQL_REFCURSOR CF_SQL_SMALLINT CF_SQL_TIME CF_SQL_TIMESTAMP CF_SQL_TINYINT CF_SQL_VARCHAR
maxLength	Optional. Maximum length of the parameter.
scale	Optional. Number of decimal places of the parameter.
null	Optional. 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. 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    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 are scoped with the name of the variable attribute that was passed to the tag.

CFML supports Oracle 8's REFERENCE CURSOR type. A REFERENCE CURSOR lets you pass a parameter by reference. Therefore, parameters that are passed by reference can be allocated and deallocated for memory within the course of one application.

Example

This example shows an Oracle 8 PL/SQL stored procedure, and the CFML code used to invoke it. 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. The second parameter pParam2 returns the rows in the DEPT table. 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>

Copyright © 2001, Macromedia Inc. All rights reserved.

---