SQL Server understand SCOPE_IDENTITY()

问题:

I have this piece of code in a stored procedure:

BEGIN
    SET @UserId = NULL;
    IF (@Username IS NOT NULL)
    BEGIN
        EXECUTE SP_ADD_USER @Username, @UserId OUTPUT;
    END
    EXECUTE SP_ADD_ALERT @Name, @AlertType, @AlertId OUTPUT;
    INSERT INTO AlertLogs (Datastamp, AlertID, UserID, NotificationMessage) 
        VALUES (@Datastamp, @AlertId, @UserId, @EmailMessage);
    SET @AlertLogId = SCOPE_IDENTITY();
END

@AlertLogId is an output parameter that I want to be assigned to the result of the last insert in AlertLogs table. Do I have to include

INSERT INTO AlertLogs (Datastamp, AlertID, UserID, NotificationMessage) 
        VALUES (@Datastamp, @AlertId, @UserId, @EmailMessage);

in a new block (a new begin/end scope) in order for SCOPE_IDENTITY() to work correctly ? (and not report for example the last ID of an inserted record done in SP_ADD_ALERT for example ?)

回答1:

In your query, SCOPE_IDENTITY() is going to return the last entered identity value into the database, for this scope.

In this instance, it will be the identity for the AlertLogs table, if this has an identity.

A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.

http://msdn.microsoft.com/en-us/library/ms190315.aspx

回答2:

You can also use an OUTPUT clause in your insert statement. This means you don't need to worry about scope and you make other (non-identity) information available from the inserted table.

Consider this simple table:

CREATE TABLE [dbo].[SampleTable](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [InsertDate] [datetime] NOT NULL,
    [Name] [nvarchar](100) NULL
) ON [PRIMARY]

With this default added:

ALTER TABLE [dbo].[SampleTable] 
    ADD CONSTRAINT [DF_SampleTable_Inserted]  
        DEFAULT (getdate()) FOR [InsertDate]

You can get values for both the default and the identity from the insert operation.

DECLARE @InsertedDetails TABLE (ID int, InsertDate DateTime);

INSERT INTO SampleTable ([Name])
    OUTPUT inserted.ID, inserted.InsertDate 
        INTO @InsertedDetails 
    VALUES ('Fred');

DECLARE @ID int; 
DECLARE @InsertDate datetime;
SELECT @ID = ID, @InsertDate = InsertDate FROM @InsertedDetails;

PRINT @ID;
PRINT @InsertDate;

Here I've just pulled the values out of the table variable and printed them.