I guess it's half a dozen of one, and six of the other.... While there is
an overhead in writing the stored procedures and CFSTOREDPROC tags, I think
you'll find that the site performance will make up for it. If this is set
up as a CFQUERY, then you're going to be making three database calls, one
for each insert, as opposed to one call to the stored procedure. If you
have a high traffic site, then this will be more noticeable.
One of the main reasons I use Stored Procedures across my sites, apart from
the performance aspect, is that of maintenance. While this particular query
might only be called in one place, you may have several calls the same.. if
these are in SP's, then you only have to make one change to a stored
procedure in order to update all the calls. If you leave them as queries
you stand the risk of missing one when you do the maintenance.
Personally, I think it's worth the extra time up front writing the stored
procedures, potentially it could save time later, particularly if you need
to speed the site up a bit!!
I wrote a utility some time ago where you could enter the database layout,
and it would create SQL scripts to generate all the tables and stored
procedures.. that saved plenty of time, it could probably be extended to
output the CFSTOREDPROC code too. Only trouble is it's in VB, and the
thought of using VB again is a scary one!! Think I'd rather write the SP's
manually!! :^)
> From: "Gary McNeel, Jr." <[EMAIL PROTECTED]>
> Reply-To: [EMAIL PROTECTED]
> Date: Thu, 6 Jul 2000 09:51:04 -0500
> To: "Cf-Talk" <[EMAIL PROTECTED]>
> Subject: Getting the ID after insert...
>
> Hello all. Hope any of you celebrating 4th of July had a good one.
>
> I have a question about the code below. This was done by a colleague of
> mine, he is pretty handy with SQL coding. One of his observations was the
> time involved in writing stored procedures and then calling them with
> CFSTORPROC. I agree, that discouraged me somewhat in the past, but I knew of
> no other way. He put this transact-sql query together and it seems solid,
> works well.
>
> My question(s): Have any of you tried this? Any problems? Can anyone see a
> potential problem?
>
> Thanks. -Gary McNeel
>
> <CFQUERY name="InsertLkpRec" datasource="#Application.DSN#">
> DECLARE @LookupID char(6)
> DECLARE @MembersID char(6)
> <!--- Insert Member Lookup Record --->
> INSERT INTO tblMemberLkp
> (DBAName, ReferredBy )
> VALUES
> ('#Form.DBAName#', '#Form.ReferredBy#')
>
> SELECT @LookupID = @@IDENTITY
>
> <!---- Members Record --->
> INSERT INTO Members
> (MemberLkpID)
> VALUES
> (@LookupID)
>
> SELECT @MembersID = @@IDENTITY
>
> <!--- Insert Site Info record --->
> INSERT INTO tblSiteInfo
> (BusName, Website, RecStatusID, RecOriginID, MembersID,
> PartnerNumber, SourceID, ApplicationDate)
> VALUES
> ('#Form.BusName#', '#Lcase(Form.Website)#', 2, 1, @MembersID,
> '#Form.PartnerNumber#', '#Form.SourceID#', getdate())
> </CFQUERY>
>
>
> Gary McNeel, Jr.
> Project Manager - DAC-Net, Research & Graduate Studies
> Rice University - Houston
> [Lovett Hall] 713-348-6266 (Primary)
> [DAC] 713-348-5184
> [M] 713-962-0885
> [H] 713-723-9240
>
> "Great spirits have always encountered violent opposition from mediocre
> minds."
> -Albert Einstein
>
> ------------------------------------------------------------------------------
> Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
> To Unsubscribe visit
> http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
> send a message to [EMAIL PROTECTED] with 'unsubscribe' in the
> body.
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
