sql - scope_identity 返回 null

Question

我正在用 SQL server 写一个 SP

ALTER PROCEDURE [dbo].[spAddUserBadge]
AS
BEGIN
    INSERT INTO dbo.SE_UserBadge
    (    
        [UID],
        BadgeID,
        BadgeAssignmentOrRenewalDate,
        BadgeExpiryDate ,
        AmountPaid, 
        Remarks ,
        AssignedBy          
    )
    VALUES
    (
        17,1, 12-12-2012, 12-12-2013, 0.00, '', ''
    )

    SELECT SCOPE_IDENTITY() AS id    
END
GO

SCOPE_IDENTITY 为 NULL。我哪里错了？

Answer 1

使用 OUTPUT运算符将插入的值输出到表变量中，然后检查这些值。

示例

ALTER PROCEDURE [dbo].[spAddUserBadge]
AS
BEGIN
    DECLARE @TBL AS TABLE(ID1 INT, ID2 INT)

    INSERT INTO dbo.SE_UserBadge
    (    
        [UID],
        BadgeID,
        BadgeAssignmentOrRenewalDate,
        BadgeExpiryDate ,
        AmountPaid, 
        Remarks ,
        AssignedBy          
    )
    OUTPUT inserted.ID1, inserted.ID2 INTO @TBL
    VALUES
    (
        17,1, 12-12-2012, 12-12-2013, 0.00, '', ''
    )

    SELECT * FROM @TBL  
END
GO