Why does @@ Identity return null?

Question

I have a .NET 2010 application hitting SQL2000 db. The code is pretty simple. When I insert a record, the record is inserted, but the identifier is not returned. The id column is an int, and this is an identification. This is where proc is stored ...

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 side ...

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

Answer 1

• You should always use SCOPE_IDENTITY ()
• NULL cannot be returned via RETURN from a stored procedure. You will get a SQL warning and it will return zero.
• ExecuteScalar searches for the 1st row, 1st column of the recordset. No entries above.

... So you would use SELECT SCOPE_IDENTITY()notRETURN SELECT SCOPE_IDENTITY()

Answer 2

ExecuteScalar

,

, RETURN

SELECT -1

SELECT CAST(SCOPE_IDENTITY() AS INT)

Answer 3

@@IDENTITY , Scope_identity().

Answer 4

, , . , . ?

, scope_identity, . , . scope_identity, .

-,

RETURN

SELECT SCOPE_IDENTITY()

, , OUTPUT . , , , . , vs resultset, .

.

Answer 5

SCOPE_IDENTITY @@IDENTITY. . Devio , . RETURN , :

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

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