SQL Server理解SCOPE_IDENTITY()

Question

我有一个存储过程中的代码片段：

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 是一个输出参数，我希望将其分配给 AlertLogs 表中最后一次插入的结果。我需要包含

吗？

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

在一个新的块（一个新的开始/结束作用域）中，为了使SCOPE_IDENTITY()正常工作，而不是报告例如在SP_ADD_ALERT中插入记录的最后一个ID？

- Ghita

Answer 1

在你的查询中，SCOPE_IDENTITY() 将返回该数据库中最后输入的标识值，对于这个作用域而言。

在这种情况下，如果 AlertLogs 表具有标识，则它将是该表的标识。

一个作用域是一个模块：存储过程、触发器、函数或批处理。因此，如果两个语句在同一个存储过程、函数或批处理中，则它们处于相同的作用域。

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

- Curtis

---

这也是我预期的。EXECUTE SP_ADD_ALERT在另一个范围内执行，所以即使它也插入了标识值，也不会被考虑在任何情况下。 - Ghita

是的，MSDN网站上确实指出这两个语句必须在同一个存储过程、函数等中。虽然我不明白为什么你会在函数中使用SCOPE_IDENTITY，因为你不能进行INSERT或EXEC操作。 - Curtis

一个函数能做任何会引发插入操作的事情吗？如果可以，那么该插入操作会影响函数中 SCOPE_IDENTITY() 的值吗？ - Matt Sach

@Matt Sach - 触发器是一个不同的作用域（如上面的引述所示），因此，除了在触发器的主体内部放置的触发器之外，触发器不能影响SCOPE_IDENTITY()的调用。 - Damien_The_Unbeliever

@MattSach - 表触发器可以被INSERT、UPDATE或DELETE调用。而用户定义函数（UDF）不能调用这些命令。 - Curtis

Answer 2

您还可以在插入语句中使用OUTPUT子句。这意味着您不需要担心作用域，并且可以从插入的表中提供其他（非标识）信息。

考虑这个简单的表：

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

加入此默认设置后：

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

您可以从插入操作中获取默认值和标识值。

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;

这里我刚刚从表变量中提取了值并将它们打印出来。

- Stephen Turner