ExecuteNonQuery how to get the Identifer

[drewex]

Hi all im using asp.net and i use the ExecuteNonQuery code to insert into table. Is there a way to get the id back from sql server. I dont want to do a select after it doesnt make sense. And if the data has duplicates it wouldnt get the right id. HELP ME PLEASE

[CardboardHammer]

You can do a SELECT to get the row id: "SELECT max(id) FROM wherever", BUT you must do it in a transaction to ensure that no other INSERT is performed on that table between your INSERT and SELECT. My recommendation would be to do both in a stored procedure.

Example:

CREATE PROCEDURE INSERT_Q
@Q varchar(20),
@id int OUTPUT
AS
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
 INSERT INTO BLAH(Q) VALUES (@Q)
 SET @id = (SELECT MAX(id) FROM BLAH)
COMMIT
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GO

EDIT: After a bit more inspection, for SQL Server 7 (works for 2000 as well), @@IDENTITY returns the last-inserted identity value (look it up in SQL Server Books Online for full details).

So you could do it like this to save a SELECT:

CREATE PROCEDURE INSERT_Q
@Q varchar(20),
@id int OUTPUT
AS
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
 INSERT INTO BLAH(Q) VALUES (@Q)
 SET @id = @@IDENTITY
COMMIT
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GO

[drewex]

Hey I found a better way to do it. But i dont have the code right now. easier solution so i dont have to do it trough a SP.