ColdFusion 9.0 Resources |
cfprocparamSyntax<cfprocparam CFSQLType = "parameter data type" maxLength = "length" null = "yes|no" scale = "decimal places" type = "in|out|inout" value = "parameter value" variable = "variable 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.
See alsocfinsert, cfprocresult, cfquery, cfqueryparam, cfstoredproc, cftransaction, cfupdate; Optimizing ColdFusion applications in Designing and Optimizing a ColdFusion Application in the Developing ColdFusion Applications HistoryColdFusion MX:
Attributes
UsageUse 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 both positional and named 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. ExampleThe 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> |