cfprocparam

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

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; 
/

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;

<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>

<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>