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 ;-)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble
Ticket application
http://www.houseoffusion.com/banners/view.cfm?bannerid=48
Message: http://www.houseoffusion.com/lists.cfm/link=i:6:2363
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
