Stored procs is not always the best solution either. There is plenty of good reason to keep your queries inside your CFML templates. If you have the luxury of a DBA then he will probably be doing everything for you in stored procs, or if you are a lone developer then you can make the choice. However people working in teams or on contracts may not have direct access to the Database server, so adding or editing stored procs can be a real problem. Unless the SP is really adding a significant performance boost or achieving something you cannot do in plain TSQL in your CFQUERY then you are probably not benefitting from it. It is also easier from a self containment and portability point of view to keep SQL within the application. Stored Procs written for MSSQL clearly wont run on MySQL.
So remember, it's the right tool for the job at the right time. Regards -- Russ Michaels my blog: http://russ.michaels.me.uk/ skype: russmichaels MSM: [email protected] Russ -----Original Message----- From: Rick Root [mailto:[email protected]] Sent: 24 September 2010 15:29 To: cf-talk Subject: Re: cfinsert/cfupdate Just for fun, here's some examples of what SQL Server receives from coldfusion when you run a pretty simple query using cfqueryparam: declare @p1 int set @p1=748592 exec sp_prepexec @p1 output,N'@P1 int,@P2 bit',N'select gallery_link_id, gallery_link_name from gallery_links where class_id = @P1 and active = @P2 order by sort',8806,1 select @p1 This exec sp_prepexec shit is one reason why, if you're writing an app that needs to scale, you probably ought to actually write your own stored procedures rather than letting CF do this kind of thing what's interesting is that I've found cases where I'd run my "query" in SQL Server Management studio, and compare the execution plan to a query like the one that SQL Server actually executes (above) and find that they're different sometimes. So writing your OWN stored procedure is the only way to be sure that the database server is doign exactly what you think its doing (or what you want it to do). rick ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337487 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
