In this situation you would want to use your @emailID variable to hold the value of Scope_Identity.
So something like the following should work...
SET @emailID = SCOPE_IDENTITY()
Instead of
Select Scope_Identity() AS emailID
HTH,
Jeff Garza
----- Original Message -----
From: Ben Densmore
To: CF-Talk
Sent: Thursday, March 11, 2004 1:52 PM
Subject: RE: Reliable way to get ID of inserted record
Can scope_identity() be used to get identities in a query such as the
piece I have below? I tried using Select Scope_Identity() AS emailID but
my query kept complaining that emailID didn't exist.
SET NOCOUNT ON
DECLARE @thisID int
DECLARE @emailID int
BEGIN transaction
/* assume this table holds main key
to insert into other tables*/
INSERT INTO sample_login
(
emailAddress
)
VALUES
(
<cfqueryparam
cfsqltype="CF_SQL_LONGVARCHAR" value="#memento.emailAddress#" />
)
SELECT @emailID = @@IDENTITY
INSERT INTO sample_customer_info
(
emailFK,
CustName,
CustCompany,
CustAddress1,
CustAddress2,
CustCity,
CustState,
CustZip,
CustCountry,
CustPhone,
CustFax,
CustWebsite
)
VALUES
(
emailID,
<cfqueryparam
cfsqltype="CF_SQL_LONGVARCHAR" value="#memento.CustName#" />,
<cfqueryparam
cfsqltype="CF_SQL_LONGVARCHAR" value="#memento.CustCompany#" />,
<cfqueryparam
cfsqltype="CF_SQL_LONGVARCHAR" value="#memento.CustAddress1#" />,
<cfqueryparam
cfsqltype="CF_SQL_LONGVARCHAR" value="#memento.CustAddress2#" />,
<cfqueryparam
cfsqltype="CF_SQL_LONGVARCHAR" value="#memento.CustCity#" />,
<cfqueryparam
cfsqltype="CF_SQL_LONGVARCHAR" value="#memento.CustState#" />,
<cfqueryparam
cfsqltype="CF_SQL_LONGVARCHAR" value="#memento.CustZip#" />,
<cfqueryparam
cfsqltype="CF_SQL_LONGVARCHAR" value="#memento.CustCountry#" />,
<cfqueryparam
cfsqltype="CF_SQL_LONGVARCHAR" value="#memento.CustPhone#" />,
<cfqueryparam
cfsqltype="CF_SQL_LONGVARCHAR" value="#memento.CustFax#" />,
<cfqueryparam
cfsqltype="CF_SQL_LONGVARCHAR" value="#memento.CustWebsite#" />
)
Ben
-----Original Message-----
From: Jeff Garza [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 11, 2004 3:13 PM
To: CF-Talk
Subject: Re: Reliable way to get ID of inserted record
I think that Rob nailed it in the posting right after yours. This is
only an issue where you have a trigger that inserts into a different
table that has an IDENTITY field in it. In that case, @@IDENTITY will
return the inserted ID from the trigger rather than your your original
insert. Also, as Rob stated, SCOPE_IDENTITY() is only available in SQL
Server 2000...
I use it because our DBA has a habit of adding triggers to our tables
without our knowledge... been burned once by it before.
As for actual usage, it's pretty much the same as @@IDENTITY:
TABLE DESIGN
========================
pk_ID int IDENTITY (1, 1) NOT NULL ,
othercol varchar(10)
========================
SQL QUERY
========================
SET NOCOUNT ON
INSERT INTO myTable (othercol)
VALUES ('myvalue')
SELECT SCOPE_IDENTITY() AS newID
SET NOCOUNT OFF
========================
This returns queryname.newID to CF...
Cheers,
Jeff Garza
----- Original Message -----
From: Jeff Small
To: CF-Talk
Sent: Thursday, March 11, 2004 11:48 AM
Subject: Re: Reliable way to get ID of inserted record
From: "Jeff Garza" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Thursday, March 11, 2004 1:35 PM
Subject: Re: Reliable way to get ID of inserted record
> Be carefull using @@IDENTITY, as it can return erroneous information
(especially if your tables have triggers associated with them). If
you are
using SQL Server 2000, use the SCOPE_IDENTITY() function instead of
@@IDENTITY.
Oooh! Use it in an example please! And how exactly *can* this produce
erroneous info?
Jeff,
always used @@IDENTITY...and is now scurred...
________________________________
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
