cfprocparam

Defines stored procedure parameters.
This tag is nested within a cfstoredproc tag.
This tag does not have a body.

  <cfprocparam cfsqltype="CF_SQL_BIGINT">

 cfprocparam(cfsqltype="CF_SQL_BIGINT");

  1. CFDocs
  2. Tags
  3. Database Tags
  4. cfprocparam
  5. CF4+
  6. Lucee
  7. BL 1.0.0+
  8. 0 Issue
  9. Edit
See Also:   cfstoredproc   cfprocresult   cfqueryparam

Attribute Reference

type string
Default: in

This attribute indicates whether the passed variable is an input, output, or input/output.
in: The parameter is used to send data to the database system only. Passes the parameter by value.
out: The parameter is used to receive data from the database system only. Passes the parameter as a bound variable.
inout: The parameter is used to send and receive data. Passes the parameter as a bound variable.
Values:
  • in
  • out
  • inout

variable string

The name of the variable that references the value of the output parameter after the stored procedure is called.
Only valid when type attribute is OUT or INOUT.

value string

The actual value that is passed to the stored procedure.

cfsqltype string
Required

SQL type to which the parameter (any type) is bound.
CFML supports the following values, where the last element of the name corresponds to the SQL data type. Different database systems might support different subsets of this list. See your DBMS documentation for information on supported parameter types.
Refer to https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-tags/tags-p-q/cfqueryparam.html for how the types are mapped.
Values:
  • CF_SQL_BIGINT
  • CF_SQL_BIT
  • CF_SQL_CHAR
  • CF_SQL_BLOB
  • CF_SQL_CLOB
  • 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 numeric
Default: 0

Maximum length of a string or character IN or INOUT value attribute. A maxLength of 0 allows any length. The maxLength attribute is not required when specifying type=out.

scale numeric
Default: 0

Number of decimal places in a numeric parameter. A scale of 0 allows any number of decimal places.

null boolean
Default: false

Whether the parameter is passed in as a null value. Not used with "OUT" type parameters.
Values:
  • true
  • false

dbVarName string

This attribute is used to specify named parameters when calling a stored procedure. If used, this attribute should be present with each cfprocparam tag of the stored procedure.
This attribute was previously deprecated then reintroduced in CF11
Databases need a variable prefix for named parameters:
: for Oracle
'@` for SQL Server.
See the following blog post for more information: https://coldfusion.adobe.com/2015/07/coldfusion-11-and-dbvarname-attribute/.

Compatibility

ColdFusion:

Version 4+ ColdFusion 11 Update 3: Restored the previously ignored dbvarname attribute.

BoxLang:

Version 1.0.0+ <bx:procparam> in BoxLang

Links more information about cfprocparam

Examples
Sample code using the cfprocparam tag 

<cfstoredproc procedure = "foo_proc" dataSource = "MY_SYBASE_TEST" username = "sa" password = "mygoodpw" dbServer = "scup" dbName = "pubs2" returnCode = "Yes" debug = "Yes">
<cfprocresult name = RS1> 
<cfprocresult name = RS3 resultSet = 3> 
<cfprocparam type = "IN" CFSQLType = "CF_SQL_INTEGER" value = "1" dbVarName = @param1> 
<cfprocparam type = "OUT" CFSQLType = "CF_SQL_DATE" variable = "FOO" dbVarName = @param2>
</cfstoredproc>

Signup for cfbreak to stay updated on the latest news from the ColdFusion / CFML community. One email, every friday.

Fork me on GitHub