IDENT_CURRENT returns the last identity value but SCOPE_IDENTITY does not

Question

I have a SQL Server as backend for an asp.net application. Multiple people might insert data in the same table 'the same time...'.

When I read the solution/answer from this post: scope_identity vs ident_current

THEN I should not use the Ident_current because I could get the id of the insert of another user.

But using Select Scope_Identity(); returns me NULL while the Select IDENT_CURRENT('tableName') returns me the correct id which I checked with SQL Server Management Studio.

The insert statement I do within a SqlTransaction. The Select IDENT_CURRENT('tableName') is done after the transaction.

What do I wrong?

UPDATE:

My insert statement which is dynamically build together by a base class:

INSERT INTO TEST (NAME) VALUES (@Name)

The command's Parameter collection has the value "xxx" and everything is fine inserted into the table.

I do NOT use stored procedures just pure SqlDataReader with C#.

commandText = "INSERT INTO TEST (NAME) VALUES ('Test1');Select Scopy_Identity();"

How can I get the last auto inc id running the above statement and should I call ExecuteNonQuery or ExecuteReader for the above because it has a INSERT and SELECT that's confusing...

Answer 1

just use the OUTPUT Clause (Transact-SQL) and you can insert the data and select back all (even multiple) identities in the same statement:

INSERT INTO TEST (NAME) OUTPUT INSERTED.YourIdentity VALUES (@Name)

working example:

DECLARE @YourTable table (YourIdentity int identity(1,1) primary key, YourCol1 varchar(5))

INSERT INTO @YourTable (YourCol1) OUTPUT INSERTED.YourIdentity VALUES ('ABC')

OUTPUT:

YourIdentity
------------
1

(1 row(s) affected)