I went through the route of inline SQL, then to SPs, and now I'm back to inline SQL. (When I say "inline", I mean anything other than SPs.)
We use many related DBs on our SQL Server. Many queries require joins across DBs. SPs caused us all kinds of problems when we moved DBs from one server to another. I decided it was best to just leave the SQL statements in the application server and not try to create SPs. The real plan is to put most, if not all, SQL statements within CFCs thereby creating some sort of encapsulation. SPs are great in the fact that you can return multiple recordsets at once. I miss that feature, but not enough to use them again. If you desire using SPs, then I would say you are doing it the right way of creating your SQL statements then converting them to SPs once you have them tied down. M!ke -----Original Message----- From: Pete Ruckelshaus [mailto:[EMAIL PROTECTED] Sent: Saturday, October 29, 2005 12:16 PM To: CF-Talk Subject: Philosophy Q: SP's or CFQUERY? I'm going back and tweaking a web app that I'm building and have learned a few stored procedure tricks (my SP skills were pretty basic before I started to play around). As a result, I'm replacing a number of CF queries (using cfqueryparam) with SQL Server stored procedures. Now, these are pretty much limited to the customer-facing (non-admin) pages, though I'm using SP's in the admin where I already have them written to return the rest sets that I need. Realizing that SP's will give greater performance, the question is this: Is it OK to write your most frequently run queries as SP's and leave the rest of the site as straight SQL queries? Or should I just make the entire site SP's? What I've been doing during development is writing straight SQL until I get what I need, then I rewrite as a SP. I've replaced a dozen or so queries that are all on the customer-facing end of things, but there are well over 100 queries still written as SQL on the admin side, and it would take a bit of effort to convert (or merge into existing SP's) those queries. Pete ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222668 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
