Macromedia coldfusion 4.5-cfml language reference User Manual

208

Optional. Data source name for the COM provider (OLE-DB only).

Optional. Yes or No. Specifies whether debug info will be listed on each statement. 
Default is No.

Optional. Yes or No. Specifies whether the tag populates 
CFSTOREDPROC.STATUSCODE with the status code returned by the stored 
procedure. Default is No.

Within a CFSTOREDPROC tag, you code 

CFPROCRESULT

 and 

CFPROCPARAM

 tags as 

necessary.

If you set the ReturnCode parameter to "YES", CFSTOREDPROC sets a variable called 
CFSTOREDPROC.STATUSCODE, which indicates the status code for the stored 
procedure. Stored procedure status code values vary by DBMS. Refer to your DBMS-
specific documentation for the meaning of individual status code values.

In addition to returning a status code, CFSTOREDPROC sets a variable called 
CFSTOREDPROC.ExecutionTime. This variable contains the number of milliseconds 
that it took the stored procedure to execute.

Stored procedures represent an advanced feature, found in high-end database 
management systems. You should be familiar with stored procedures and their usage 
before implementing these tags.

...
<!--- The following 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>