# 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/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_SMALLINT
CF_SQL_BIT | CF_SQL_INTEGER | CF_SQL_TIME
CF_SQL_CHAR | CF_SQL_LONGVARCHAR | CF_SQL_TIMESTAMP
CF_SQL_DATE | CF_SQL_MONEY | CF_SQL_TINYINT
CF_SQL_DECIMAL | CF_SQL_MONEY4 | CF_SQL_VARCHAR
CF_SQL_DOUBLE | CF_SQL_NUMERIC |
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 type. 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.

### Example

    ...
    <!--- The following view-only example executes a Sybase stored procedure
           that returns three result sets, two of which we want. The
           stored procedure returns the status code and one output
           parameter, which we display. We use named notation
           for the parameters. --->
    <!--- CFSTOREDPROC tag --->
    <CFSTOREDPROC PROCEDURE="foo_proc"
        DATASOURCE="MY_SYBASE_TEST"    USERNAME="sa"
        PASSWORD=""    DBSERVER="scup"    DBNAME="pubs2"
        RETURNCODE="YES"    DEBUG>
    <!--- CFPROCRESULT tags --->
    <CFPROCRESULT NAME = RS1>
    <CFPROCRESULT NAME = RS3 RESULTSET = 3>
    <!---  CFPROCPARAM tags --->
    <CFPROCPARAM TYPE="IN"
        CFSQLTYPE=CF_SQL_INTEGER
            VALUE="1"    DBVARNAME=@param1>

    <CFPROCPARAM TYPE="OUT"    CFSQLTYPE=CF_SQL_DATE
        VARIABLE=FOO DBVARNAME=@param2>
    <!--- Close the CFSTOREDPROC tag --->
    </CFSTOREDPROC>
    <CFOUTPUT>
    The output param value: '#foo#'
    <br>
    </CFOUTPUT>
    <h3>The Results Information</h3>
    <CFOUTPUT QUERY = RS1>#NAME#,#DATE_COL#
    <br>
    </CFOUTPUT>
    <P>
    <CFOUTPUT>
    <hr>
    <P>Record Count: #RS1.RecordCount# >p>Columns: #RS1.ColumnList#
    <hr>
    </CFOUTPUT>
    <CFOUTPUT QUERY=RS3>#col1#,#col2#,#col3#
    <br>
    </CFOUTPUT>
    <P>
    <CFOUTPUT>
    <hr>
    <P>Record Count: #RS3.RecordCount# <P>Columns: #RS3.ColumnList#
    <hr>
    The return code for the stored procedure is:
      '#CFSTOREDPROC.STATUSCODE#'<br>
    </CFOUTPUT>
    ...