Hmmm... I was under the (apparently mistaken) belief that you could only guarantee that @@identity would return the correct ID when run in a storedproc - since @@identity is a global and since SQL is a multithreaded app as is CF that you might possibly have someone do an insert on the server in the brief space between the INSERT and the SELECT statements. In which case the @@Identity would actually return the recordID of the second users INSERT and not the first.
Is this incorrect? Tacobells solution is the following:
<cfquery ...>
DECLARE @pk INT
INSERT statement here
SET @pk = SCOPE_IDENTITY()
SELECT @pk AS primaryKey
</cfquery>
You actually referrenced that is the last part of your comment... can you (or someone else for that matter) provide evidence or links to conclusively support one method or the other in most cases (I realize that other steps may need to be taken in the case of really complex transactions or triggers on the server side). You've earned some points already reggi635 - and if Tacobell doesn't choose to comment here I'll award you instead with the whole thing. I'm really just looking for a discussion on which method is truly better and why. Particularly in a MS SQL CF MX situation. I'm just convinced that my old method (transactions with a select MAX(ID) is definitely NOT the best, but I would love to hear some evidence as to why a SCOPE_IDENTITY() method is preferrable or not to @@Identity.
Main Topics
Browse All Topics
by: reggi635Posted on 2004-09-29 at 21:52:13ID: 12187008
No offenses tacobell. Watch out dude you already have some fans in here :-)
My Try :
<cfquery name="qry" datasource="dsn">
INSERT INTO TBLMYDATA (TextCol, NumCol, BitCol)
VALUES
<cfqueryparam cfsqltype="CF_SQL_VARCHAR"
<cfqueryparam cfsqltype="CF_SQL_INTEGER"
<cfqueryparam cfsqltype="CF_SQL_BIT" value="#BitCol#">
SELECT @@identity AS newID
</cfquery>
<cfoutput>#qry.newID#</cfo
might wanna also check the scope_identity in case u have a trigger.
REGGI