SQL Server 了解 SCOPE_IDENTITY() [英] SQL Server understand SCOPE_IDENTITY()

问题描述

我在存储过程中有这段代码:

BEGINSET @UserId = NULL;如果(@用户名不为空)开始执行 SP_ADD_USER @用户名，@UserId 输出；结尾执行 SP_ADD_ALERT @Name、@AlertType、@AlertId 输出；INSERT INTO AlertLogs (Datastamp, AlertID, UserID, NotificationMessage)值(@Datastamp，@AlertId，@UserId，@EmailMessage)；SET @AlertLogId = SCOPE_IDENTITY();结尾

@AlertLogId 是一个输出参数，我希望将其分配给 AlertLogs 表中最后一次插入的结果.我是否必须包括

INSERT INTO AlertLogs (Datastamp, AlertID, UserID, NotificationMessage)值(@Datastamp，@AlertId，@UserId，@EmailMessage)；

在一个新块(一个新的开始/结束范围)中，以便 SCOPE_IDENTITY() 正常工作?(例如，不报告例如在 SP_ADD_ALERT 中完成的插入记录的最后一个 ID?)

解决方案

在您的查询中，SCOPE_IDENTITY() 将针对此范围将最后输入的标识值返回到数据库中.>

在这种情况下，它将是 AlertLogs 表的标识，如果它有标识的话.

<块引用>

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

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

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 ?)

解决方案

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

这篇关于SQL Server 了解 SCOPE_IDENTITY()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！