In SQL Server the autonumber (which is called IDENTITY in SS) is available
in the system variable @@IDENTITY. So, you could code your SP like so:
CREATE PROC myProc
AS
BEGIN TRAN
INSERT INTO myTable
VALUES (1,2,3)
SELECT @@IDENTITY AS NewAutoNumber
COMMIT TRAN
Then, when you call the SP, you will receive a result set that contains one
column (called NewAutoNumber) and one row (which contains the actual number
generated). Make sure to wrap this in a transaction or you could get
unexpected results.
You could also use a return code or an output variable to return the value
to CF, but I find that most modification SPs that I write don't return any
results, so it's simple to use the result set to return this value.
Bob
-----Original Message-----
From: Joseph Higgins [mailto:[EMAIL PROTECTED]]
Sent: Saturday, April 21, 2001 12:28 AM
To: Fusebox
Subject: OT SP
When using stored procedures with SQL2K and CF, how do you get back the
autonumber of the last inserted record?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
