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

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

exec spMyStoredProc param1,param2

</cfquery>

MySQL

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?

This topic has been closed for replies.

Inspiring

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

Known Participant

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?

Inspiring

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

Sign up