> 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>
There isn't anything really wrong with that, from a general standpoint. ODBC
2 and later allow you to run SQL batches, and as long as your ODBC driver
supports it (and the SQL Server one certainly does), you can write batches
using T-SQL.
However, I don't really think it takes any longer to write that as a stored
procedure, which will give you better results generally. SQL Server can
perform better optimization of stored procedures than it can with ODBC
passthrough queries, usually. I've seen performance and reliability
increases using stored procedures for INSERTs, believe it or not!
Also, from a security standpoint, you'd generally want to lock down your
ODBC datasources as much as possible; stored procedures give you an easy way
to do this.
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
voice: (202) 797-5496
fax: (202) 797-5444
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/[email protected]/
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.
