Andres,
Here's a snippet from one of my stored procs. I don't think you necessarily
need the brackets around datatypes, but I like to use them. Default values
are declared with the equals sign. These are all input parameters except
the last one.
CREATE PROCEDURE [dbo].[proc_VisitInsertUpdate]
@Action [varchar](10),
@ViTransectID [int],
@ViDate [datetime],
@ViAgentIDObs2 [int] = NULL,
@ViAgentIDEnt [int],
@ViCountSeason [varchar](12) = 'Summer',
@NewVisitID [int] output
.....and here's part of a CF page that calls it:
<CFSTOREDPROC PROCEDURE="proc_VisitInsertUpdate" DATASOURCE="#DataSource#">
<CFPROCPARAM TYPE="In" DBVARNAME="@Action" VALUE="Insert"
CFSQLTYPE="CF_SQL_VARCHAR" NULL="No">
<CFPROCPARAM TYPE="In" DBVARNAME="@ViTransectID" VALUE="
#Session.TransectID#" CFSQLTYPE="CF_SQL_INTEGER" NULL="No">
<CFPROCPARAM TYPE="In" DBVARNAME="@ViDate" VALUE="
#CreateOdbcDate(Session.ViDate)#" CFSQLTYPE="CF_SQL_TIMESTAMP" NULL="No">
and so on. I set the "Action" variable to help decide an action in the
stored proc. I always assumed the value would be "" in the stored proc if
that's what you passed. But you must include a procparam for it or the
stored proc won't work. Even if you're passing values in another form, all
the params need to be passed. e.g. :
<CFQUERY Name="MyQuery" Datasource="#MyDataSource#'>
EXECUTE proc_VisitInsertUpdate 'Insert', 19, '2000-10-20', and so
on.....
</CFQUERY>
HTH,
Mark
[EMAIL PROTECTED]
What is the equivalent of <cfparam name="var" default=""> inside a stored
procedure?
i want to pass a value to a stored procedure, but the value, and the
variable that holds it, may not be passed all the time.
I want the stored procedure to assign a value of "" to the variable if no
value is passed to it. Then, depending on this variable, it needs to
perform
either one of two queries.
can someone give me pointers on how to do this??
thanks
andres leon
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
