CFProcParam - MX7

[paross1]

Well, you don't have to abandon using them if you don't mind providing the
unused parameters with null="no". I've been doing this for years (since CF
4.5), as Oracle never did allow for me to do this.

Phil

[Steve Sommers]

If this was simply an internal application using a single database, this might
not be an issue. The problem is that our application supports multiple
customers and each customer has their own database; each of these databases may
be at a different schema version and as we add and remove columns over time, we
try to arrange the columns in a logical (business). Requiring all databases be
at the same schema version is not possible in our environment (we have
literally 1000's of databases and our SLA does not allow downtime while we
synchronize versions) and having a single application support multiple database
schema's using positional parameters would be a nightmare.

With this limitation, I'm not sure the advantages in using CFStoredProc as
opposed to CFQuery?

<cfquery datasource="#APPLICATION.DSN#" dbtype="ODBC">
exec ContactInsProc
@FirstName='#FORM.ContactFirstName#',
@PostalCode='#FORM.PostalCode#'
</cfquery>

[paross1]

The only advantage that comes to mind for using CFSTOREDPROC over CFQUERY would
be if you intended to return values or result sets from your stored procedure.
However, if you are just calling a stored proc to perform a function without
returning anything, then you are probably OK using CFQUERY.

Phil

[Steve Sommers]

I assume you are talking about a speed advantage? I current pass values back as
a result set:

<cfquery name="qContact" datasource="#APPLICATION.DSN#" dbtype="ODBC">
exec ContactInsProc
@FirstName='#FORM.ContactFirstName#',
@PostalCode='#FORM.PostalCode#'
select @@identity as ContactID
</cfquery>

[paross1]

Sorry, I had my Oracle hat on so my comments about parameter passing and result
sets really don?t apply in this case. If the execution time is comparable
between the two methods for your particular procedures, then use CFQUERY by all
means.

Phil