Sprocs are faster if you are doing multiple things in 1 call. The idea is you are in the database already so go ahead and let the database do what it does best. For general selects, inserts, updates, and deletes keeping it in the app is fine.
It does come down to personal preference though. I used to use them all of the time because the dba would spit them out. Well, I built a cfc generator that created my cfc's with all of the crud I need (and even grabs a view if it is named vMyTable [table name with v in front of it]) which makes updates way faster. Before I had to update the sproc, cfc(s), and calls to cfcs if I made 1 change to my table. Now I just run my generator again if I make any table changes. So, for me...I'm done with sprocs unless there is some sort of mid to major processing I need done (cursors, etc). On 3/2/06, Robertson-Ravo, Neil (RX) <[EMAIL PROTECTED]> wrote: > > No benefit really. Not in this instance. > > > > -----Original Message----- > From: Mike | NZSolutions Ltd [mailto:[EMAIL PROTECTED] > Sent: 01 March 2006 21:39 > To: CF-Talk > Subject: Stored Procedures and when to use them > > Hi guys, > > I am just getting my head around stored procedures in SQL Server. One of > the things I am trying to understand is when I should be using them. > > For example, on my clients homepage I wish to pull 2 random product > records... > > <cfquery name="getRandomProducts" datasource="#Request.App.dsn#" > password="#Request.App.DBpassword#" username="#Request.App.DBusername#"> > SELECT TOP 2 products.product_id, products.product_code, > products.product_price, products.product_price_sale, > products_description.product_title > FROM products INNER JOIN products_description > ON products.product_id = products_description.product_id > WHERE products.product_status = 1 > AND products.product_display = 1 > ORDER BY newid() > </cfquery> > > Is there any advantage having this type of query in a SP over calling a > simple CFC ?? > > Also, if anyone has any good references for me to check out... > > mike > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:233850 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=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
