Contents > CFML Reference > ColdFusion Tags > cfprocparam

cfprocparam

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

Database manipulation tags

<cfprocparam 
   type = "in" or "out" or "inout"
   variable = "variable name"
   value = "parameter value"
   CFSQLType = "parameter datatype"
   maxLength = "length"
   scale = "decimal places" 
   null = "Yes" or "No">

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

ColdFusion MX:

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

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 MX supports positional parameters only and 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 reference cursors. Instead, use the cfprocresult tag.

The following example shows how to invoke an Oracle 8 PL/SQL stored procedure. It makes use of Oracle 8 support of the Reference Cursor type.

The following package, Foo_Data, houses a procedure refcurproc that declares output parameters as Reference Cursor:

• Parameter pParam1 returns the rows in the EMP table
• 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 the cfstoredproc tag can call this procedure, 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>

Contents > CFML Reference > ColdFusion Tags > cfprocparam

ColdFusion 9 | ColdFusion 8 | ColdFusion MX 7 | ColdFusion MX 6.1 | ColdFusion MX | Forums | Developer Center | Bug Reporting

Version 6.1

Comments are no longer accepted for ColdFusion MX 6.1. ColdFusion 8 is the current version.

Comments

Add Comment

Rob Rusher said on Dec 8, 2003 at 8:21 AM :

The docs state that a value of zero in the scale attribute will allow any number of decimal places. This is incorrect. Not setting the scale attribute will default to zero and limit the value passed in to an integer.

You must add a value to the scale attribute for decimal numbers.

CFH4X0R said on Jan 12, 2004 at 2:36 AM :

somebody have any problem using <cfprocparam with type ="out" cfsqltype="cf_sql_date"
??

If I use this instance:

<cfprocparam type="out" cfsqltype="cf_sql_date" dbvarname="startDate" variable="cfstartDate">

I only recibe this error

Error Occurred While Processing Request
1

The error occurred in \\XXX.cfm: line 53

Please help!

isaac dealey said on Jan 24, 2004 at 10:02 PM :

If you're using MS SQL Server you need to use cf_sql_timestamp for _all_ date, time and datetime columns and parameters. SQL Server doesn't understand any of the other date or date/time types.

Showfax TJ said on Jan 30, 2004 at 9:07 PM :

This tag needs to support a default attribute, where having default="yes" will pass the string "default" as an argument to the SP. for example if in my SP I have

create proc [dbo].[sp_EchoDate]
@thedate datetime='1/1/1970'
as
print @thedate
go

and in CF I do

<cfstoredproc procedure="[dbo].[sp_EchoDate]" datasource="***">
<cfprocparam type="in" cfsqltype="cf_sql_date" value="#somevar#" null="#(len(somevar) is 0)#" />
</cfstoredproc>

Doing the above only puts the value NULL as the SP arguement, and the SP will use the value NULL instead of the default. This should be fixed, as SQL 2000 allows for opting to use the default value of the arguement via use of the "default" keyword.

-Thomas Olaes
Breakdown Services
Application Developer

halL said on Feb 2, 2004 at 7:39 AM :

We have added an enhancement request (54101) for this change.

davidsatz said on Apr 14, 2004 at 8:46 AM :

For SQL Server, what are you supposed to select for nvarchar columns ?

jrunrandy said on Apr 14, 2004 at 9:54 AM :

Try CF_SQL_VARCHAR, per http://www.blinex.com/~sam/CF_SQL_TYPES.cfm

Shiny7 said on Aug 1, 2004 at 9:34 AM :

null="Yes" is supposed to ignore the value attribute, right? Then why, when I have this:

<cfprocparam value="#attributes.photo#" maxlength="50" cfsqltype="CF_SQL_VARCHAR" null="Yes">

I get the error: "Element PHOTO is undefined in ATTRIBUTES." ?

son42uk said on Sep 29, 2004 at 7:03 AM :

We have stored procedures with "TABLE OF datatype" kind of output
parameters. We've been using CF 5 and used the MAXROWS attribute.
Now with CF MX, the MAXROWS attribute has been obsoleted. How can
we migrate code without having to rewrite the Stored Procedures to use
reference cursors?

rmdv said on Oct 1, 2004 at 1:09 AM :

We have exactly the same problem, using the "TABLE OF datatype" method in our existing code. Please help!

No screen name said on Oct 14, 2004 at 11:31 AM :

Ditto... we also need a solution to the 'table of datatype' issue.

tanguyr said on Nov 25, 2004 at 8:54 AM :

It should be noted that the value parameter of this tag must be a string - this is a problem when you try to pass binary data in to a stored procedure with CFSQLType="CF_SQL_BLOB". If you create a variable using <cffile action="READBINARY" ... variable="myBinaryFile"> and then try to pass it to a <cfstoredprocparam CFSQLType="CF_SQL_BLOB" value="#myBinaryFile#" ...> you will get an error reporting that "The value cannot be converted to a string because it is not a simple value."

gana78 said on Jan 12, 2005 at 12:58 AM :

There appears to be a bug in 6.1 with regards to returning unicode data using OUT parameters.

<cfprocparam type="out" cfsqltype="cf_sql_varchar" variable="foo"> is not returning unicode data properly. Chinese, Russian etc show up as ???.

Exact same code works fine in our 6.0 server.

(Yes, the string format unicode option is checked on both servers for the ODBC connection).

RSS feed | Send me an e-mail when comments are added to this page | Comment Report

Current page: http://livedocs.adobe.com/coldfusion/6.1/htmldocs/tags-b16.htm

Add Comment