cfprocparam

Last update:

May 18, 2026

Note:

This tag is unsupported in CFFiddle.

Description

Defines stored procedure parameters. This tag is nested within a cfstoredproc tag.

Syntax

<cfprocparam 

CFSQLType = "parameter data type" 

maxLength = "length" 

null = "yes|no" 

scale = "decimal places" 

type = "in|out|inout" 

value = "parameter value" 

variable = "variable name"

dbvarname = "proc parameter name">

Note:

You can specify this tag's attributes in an attributeCollection attribute whose value is a structure. Specify the structure name in the attributeCollection attribute and use the tag's attribute names as structure keys.

History

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

ColdFusion MX:

The maxrows attribute is obsolete.
Changed the dbvarname attribute behavior: it is now ignored for all drivers. ColdFusion uses JDBC 2.2 and does not support named parameters. This is deprecated.
Changed the maxLength attribute behavior: it now applies to IN and INOUT parameter values.

Attributes

Attribute	Req/Opt	Default	Description
CFSQLType	Required		SQL type to which the parameter (any type) is bound. ColdFusion 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. For information on supported parameter types, see your DBMS documentation. CF_SQL_BIGINT CF_SQL_BIT CF_SQL_BLOB CF_SQL_CHAR 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 For a mapping of ColdFusion SQL data types to JDBC data types, see cfqueryparam .
maxLength	Optional	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.
null	Optional	no	Whether the parameter is passed in as a null value. Not used with OUT type parameters. yes: tag ignores the value attribute. no
scale	Optional	0	Number of decimal places in numeric parameter. A scale of 0 limits the value to an integer.
type	Optional	in	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.
value	Required if type = "IN"		Value that ColdFusion passes to the stored procedure. This is optional for inout parameters.
variable	Required if type = "OUT" or "INOUT"		ColdFusion variable name; references the value that the output parameter has after the stored procedure is called. This is ignored for in parameters.
dbVarName	Optional		When sending the named parameters to a stored procedure, this attribute can be used to specify the name of the parameter. If used, this attribute be present with each cfprocparam tag of the stored procedure. In ColdFusion 11, the JVM flag ( -Dcoldfusion.ignoredbvarname=true) can be used to disable this attribute. However, in ColdFusion (2016 release), this JVM flag is not supported. Note: Databases need a variable prefix for named parameters. : for Oracle @ for SQLServer

Usage

Use this tag to identify stored procedure parameters and their data types. Code one cfprocparam tag for each parameter. The parameters that you code vary based on parameter type and DBMS. ColdFusion supports positional parameters. If you use positional parameters, you must code cfprocparam tags in the same order as the associated parameters in the stored procedure definition.Output variables are stored in the ColdFusion variable specified by the variable attribute.

You cannot use the cfprocparam tag for Oracle 8 and 9 reference cursors. Instead, use the cfprocresult tag.

Example

The following examples list the equivalent Oracle and Microsoft SQL Server stored procedures that insert data into the database. The CFML to invoke either stored procedure is the same.

The following example shows the Oracle stored procedure:

CREATE OR REPLACE PROCEDURE Insert_Book ( 

arg_Title Books.Title%type, 

arg_Price Books.Price%type, 

arg_PublishDate Books.PublishDate%type, 

arg_BookID OUT Books.BookID%type) 

AS 

num_BookID NUMBER; 

BEGIN 

SELECT seq_Books.NEXTVAL 

INTO num_BookID 

FROM DUAL; 

INSERT INTO 

Books ( 

BookID, 

Title, 

Price, 

PublishDate ) 

VALUES ( 

num_BookID, 

arg_Title, 

arg_Price, 

arg_PublishDate ); 

arg_BookID := num_BookID; 

END; 

/

The following example shows the SQL Server stored procedure:

CREATE PROCEDURE Insert_Book ( 

@arg_Title VARCHAR(255), 

@arg_Price SMALLMONEY, 

@arg_PublishDate DATETIME, 

@arg_BookID INT OUT) 

AS 

BEGIN 

INSERT INTO 

Books ( 

Title, 

Price, 

PublishDate ) 

VALUES ( 

@arg_Title, 

@arg_Price, 

@arg_PublishDate ); 

SELECT @arg_BookID = @@IDENTITY; 

END;

You use the following CFML code to call either stored procedure:

<cfset ds = "sqltst"> 

<!--- <cfset ds = "oratst"> ---> 

<!--- If submitting a new book, insert the record and display confirmation ---> 

<cfif isDefined("form.title")> 

<cfstoredproc procedure="Insert_Book" datasource="#ds#"> 

<cfprocparam cfsqltype="cf_sql_varchar" value="#form.title#"> 

<cfprocparam cfsqltype="cf_sql_numeric" value="#form.price#"> 

<cfprocparam cfsqltype="cf_sql_date" value="#form.price#"> 

<cfprocparam cfsqltype="cf_sql_numeric" type="out" variable="bookId"> 

</cfstoredproc> 

<cfoutput> 

<h3>'#form.title#' inserted into database. The ID is #bookId#.</h3> 

</cfoutput> 

</cfif> 

<cfform action="#CGI.SCRIPT_NAME#" method="post"> 

<h3>Insert a new book</h3> 

Title: 

<cfinput type="text" size="20" required="yes" name="title"/> 

<br/> 

Price: 

<cfinput type="text" size="20" required="yes" name="price" validate="float"/> 

<br/> 

Publish Date: 

<cfinput type="text" size="5" required="yes" name="publishDate" validate="date"/> 

<br/> 

<input type="submit" value="Insert Book"/> 

</cfform>

Another example:

<cfstoredproc procedure = "foo_proc" 

dataSource = "MY_SYBASE_TEST" username = "sa" 

password = "" 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>

Was this page helpful?

We're glad. Tell us how this page helped.

Found the answer to my problem Understood the instructions Liked the feature

Other suggestions

We're sorry. Can you tell us what didn't work for you?

Didn't find the answer to my problem Couldn't understand the instructions Didn't like the feature

Other suggestions

Thank you for your feedback. Your response will help improve this page.

Was this helpful?

We are sorry the content didn't meet your needs.

Share additional feedback to help us improve.

0/255 | Character limit exceeded.

Thank you so much for sharing your feedback!