cfstoredproc vs. Exec (SQL Server) or Call (MySQL)

[rlinhart68]

Just out of curiosity...

Why go through the process of adding a cfstoredproc with associated cfprocparams and cfprocresults when I could simply use EXEC or CALL commands?

SQL Server

<cfquery>

     exec spMyStoredProc param1,param2

</cfquery>

MySQL

<cfquery>

     call spMyStoredProc param1,param2

</cfquery>

It sure would seem to be a lot simpler doing it this way.

Could it have something to do with type validation or database server permissions?

[Dan_Bracuk]

I think it's a simple matter of there being more than one way to do something. 

[rlinhart68]

Why ever use cfstoredproc, etc. then?

I was thinking possibly because of caching, but cfquery has practically the same attributes as cfstoredproc.

Or perhaps for those database servers that do not allow direct calling of stored procedures?

[Adam Cameron.]

As you say, not all DBs will allow query access (indeed: an awful lot of them won't).

Also... try running a proc that returns multiple recordsets using <cfquery>

Plus the JDBC driver handles proc calls one way, and dynamic SQL statements another way.

Using <cfquery> to call a proc is like using a hammer to drive in screws.  It's possible, but you're not using the right tool for the job.

--

Adam