Why is @@Identity returning null?

Question

I have a .NET 2010 app hitting a SQL2000 db. The code is pretty basic. When I insert a record, the record is inserted, but the id is not returned. The id column is an int and it is an Idetity. Here is the stored proc...

ALTER PROCEDURE Insert_Vendor

@CorpID as varchar(255), 
@TaxpayerID as varchar(255)
AS

Insert into  dbo.Vendor
(
vdr_CorpID, 
vdr_TaxpayerID
)
values
(
@CorpID, 
@TaxpayerID
)

        IF @@error <> 0 
            BEGIN
                RETURN -1
            END
        ELSE 
        RETURN @@Identity
GO

And on the receiving end...

 int myID = (int)(db.ExecuteScalar(dbCommand));

Answer 1

you have to call @@IDENTITY right after the insert, use Scope_identity() instead.

Answer 2

Because your question leaves out a lot of details I will just mention a few possible ways around this as it seems impossible to answer a question without all the details. But it's your first time here so you'll get better. You will right?

Anyways first I would say you should always use scope_identity as it is safer. There could be things going on behind the scenes with triggers that could cause this real problems. Stick with scope_identity and you shouldn't have to worry.

Second I would suggest instead of

RETURN

use

SELECT SCOPE_IDENTITY()

Lastly I would say why not just use an OUTPUT parameter vs returning a result. I don't have anything to support this next statement but I would think it is better. Again no proof on that but it just seems like less overhead with output parameter vs resultset that comes with schema.

Just my thoughts.

Answer 3

• You should always use SCOPE_IDENTITY()
• NULL can't be returned via RETURN from a stored proc. You'd get a SQL warning and it would return zero.
• ExecuteScalar looks for the 1st row, 1st column of a recordset. There is no recordset above

... So you'd use SELECT SCOPE_IDENTITY() not RETURN SELECT SCOPE_IDENTITY()

Answer 4

ExecuteScalar

executes the query, and returns the first column of the first row in the result set returned by the query

So you need to re-write the RETURN statements as

SELECT -1

and

SELECT CAST(SCOPE_IDENTITY() AS INT)

respectively

Answer 5

I personally would recommend using SCOPE_IDENTITY instead of @@IDENTITY. That being said the problem is in the stored procedure. Devio above was correct the execute scalar is looking for the first column of the first row. The RETURN statement will not do this so you will need to use either one of the below items:

    IF @@error <> 0 
    BEGIN
        Select -1
    END
    ELSE 
    Select @@Identity

or:

    IF @@error <> 0 
    BEGIN
        Select -1
    END
    ELSE 
    Select SCOPE_IDENTITY()