I generally use <cfquery> to call my stored procedures. Others prefer
<cfstoredproc>. You cannot use <cfquery> if you want to:
- capture the return code from the SP
- capture an output parameters of the SP
- return more than one result set
If you don't need to do any of the above (which is about 90% of the time for
me), you could use the following code:
<cfset myNumber = 55.55>
<cfset myString = "Nice stored procedure, Buddy.">
<cfset myDate = "2001-03-30">
<cfquery name="myQuery" datasource="myDSN">
Exec MyStoredProcedure @NumberParam=#Val(myNumber)#,
@StringParam='#myString#', @DateParam='#myDate#'
</cfquery>
Then you just use the query as you would normally, e.g.,
<cfoutput query="myQuery>
etc.
You should also use the T-SQL statements SET NOCOUNT ON before running the
SELECT and SET NOCOUNT OFF after running the SELECT. You could include
these inside your stored procedure, or you can include them in you call, so
it would look like:
<cfquery name="myQuery" datasource="myDSN">
SET NOCOUNT ON
Exec MyStoredProcedure @NumberParam=#Val(myNumber)#,
@StringParam='#myString#', @DateParam='#myDate#'
SET NOCOUNT OFF
</cfquery>
Hope that helps,
Bob
-----Original Message-----
From: Jaime Perez [mailto:[EMAIL PROTECTED]]
Sent: March 29, 2001 6:06 PM
To: CF-Community
Subject: Re: Stored procedures...when would you
Can someone show some code where you call a stored procedure
that receives the following parameters: number, string, date and then
the SP returns the results set. I have seen some of the sample code
from the Cold Fusion manuals but I still am having a few problems
understanding.
Can someone show me some simple SP and CF code to show me?
The database I work with is MS SQL 7
Sincerely,
Jaime
Ben Forta wrote:
> But point worth noting, if you are using SQL Server you could actually be
> using SP's without knowing it. There is a driver option (often on by
> default) in the SQL Server Control Panel applet that instructs Sql Server
to
> auto-generate a SP for submitted SQL statements. These persist for the
> active connection, so you could be reusing SP's and getting some of the
> benefit of doing so already.
>
> --- Ben
>
> -----Original Message-----
> From: Michael Smith [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, March 29, 2001 3:37 PM
> To: CF-Community
> Subject: Re: Stored procedures...when would you
>
> Great discussion on SPs and Views! I would add another reason not to do
> SPs - they are harder to debug from CF that
> straight SQL - when it doesn't work you don't get a good error message
from
> CF... Of course the regular CFQUERY
> error message isn't great either but at least you can cut and paste the
SQL
> back into your query builder to test...
>
> I think writing SPs takes longer than inline SQL - with two enviroments
(CF
> and SQL ent manager) and testing. Only
> makes sense on speed or load critical code. Straight SQL is better for
> prototyping too for this reason.
>
> - Michael Smith, TeraTech, Inc http://www.teratech.com/
>
> Angel Stewart wrote:
>
> > NOT want to use stored procedures to run queries etc. on a database?
> >
> > Wouldn't a stored procedure almost always run faster than one done
through
> > CF?
> >
> > -Gel
> >
> >
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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
