You could do it that way yeah...(and I like the way you chose the 'correct' method for getting the ID) - it is personal preference but the other pointers remain.
-----Original Message----- From: Ray Thompson [mailto:[EMAIL PROTECTED] Sent: 15 August 2005 14:42 To: SQL Subject: RE: Using IN clause with a stored procedure For inserting new records and retrieving the key of the inserted record can be done with a single query. <cfquery> INSERT INTO sometable (column_1, column_2) VALUES (value_1, value_2); select InsertedID = Scope_Identity() </cfquery> Will get the identity column of the record just inserted and does only return one result set. There are good and valid arguments for both methods, inline SQL and SP. There is no right or wrong and if the code works then all is well. Ray Thompson Tau Beta Pi (www.tbp.org) The Engineering Honor Society 865-546-4578 -----Original Message----- From: Robertson-Ravo, Neil (RX) [mailto:[EMAIL PROTECTED] Sent: Monday, August 15, 2005 8:57 AM To: SQL Subject: RE: Using IN clause with a stored procedure True and not true. This is only the case when you are adding input params. With inline SQL you can only bring back 1 resultset at a time and I think this is major drawback. You are also limited T-SQL wise on what you have within it i.e. performing a select and then immediately afterward @@rowcount or perform an update and then use @@identity (or alike) - you have to do two blocks which gives you two result sets unnecessarily. I personally think the use of SP;s make it easier to manage an application than one which utilises cfquery throughout. SP's are more efficient no question as if you were to perform say a data retrieve some data, update a datetime stamp, and then insert a new record all 3 tasks once a second as ad-hoc SQL this would result in 259,200/day independent database request vs. 86,400/day if all were encapsulated in a stored procedure. Using an SP is a more effective use of network bandwidth. SP's also have a cached execution plan where as inline SQL does not - so you will gain overall speed increase. Again it is preference if you like inline SQL go for ;-) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Get Instant Hacker Protection, Virus Detection, Antispam & Personal Firewall. http://www.houseoffusion.com/banners/view.cfm?bannerid=62 Message: http://www.houseoffusion.com/lists.cfm/link=i:6:2364 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/6 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:6 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
