代码之家 › 专栏 › 技术社区 › libpekin1847

SCOPE-IDENTITY()返回NULL而不是IDENTITY值T-SQL

t-sql sql-server

libpekin1847 · 技术社区 · 7 月前

我正在使用动态SQL将默认值插入表中,并希望返回 SCOPE_IDENTITY() 我的代理密钥的值。然而, 范围() 正在返回 NULL 出于某种原因。我错过了什么吗?

谢谢!

DECLARE @SQL NVARCHAR(MAX),
        @SurrogateKey INT,
        @SchemaName VARCHAR(128) = 'dim',
        @TableName VARCHAR(128) = 'Sales',
        @NaturalKeyColumn VARCHAR(128) = 'SalesID',
        @NaturalKeyValue VARCHAR(40) = '100',
        @SurrogateKeyColumn VARCHAR(128) = 'SalesKey';

SET @SQL = N'SELECT @SurrogateKey = ' + QUOTENAME(@SurrogateKeyColumn) + 
           ' FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + 
           ' WHERE ' + QUOTENAME(@NaturalKeyColumn) + ' = @NaturalKeyValue';

EXEC sp_executesql @SQL, N'@SurrogateKey INT OUTPUT, @NaturalKeyValue VARCHAR(40)', @SurrogateKey OUTPUT, @NaturalKeyValue;

IF @SurrogateKey IS NULL
BEGIN
    BEGIN TRANSACTION;

    SET @SQL = N'INSERT INTO ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + 
               ' (' + QUOTENAME(@NaturalKeyColumn) + ', InferredFlag)
                VALUES (@NaturalKeyValue, 1)';

    EXEC sp_executesql @SQL, N'@NaturalKeyValue VARCHAR(40)', @NaturalKeyValue;

    -- Check if the INSERT was successful
    IF @@ROWCOUNT > 0
    BEGIN
        SET @SQL = N'SELECT @SurrogateKey = SCOPE_IDENTITY()';

        EXEC sp_executesql @SQL, N'@SurrogateKey INT OUTPUT', @SurrogateKey OUTPUT;
    END

    COMMIT TRANSACTION;
END

SELECT @SurrogateKey;

1 回复 | 直到 7 月前

Charlieface 7 月前

SCOPE_IDENTITY() 不出所料,它只在一个范围内工作。您需要创建并执行一个动态SQL批处理。这也更有效。

代码的其他问题:

交易在错误的地方启动,需要在 SELECT .
您需要向添加锁定提示 选择 ,否则将使用共享锁,这对您没有帮助。你需要 SERIALIZABLE 和 UPDLOCK 在这里。
对象和列名应存储在 sysname 变量,它是的别名 nvarchar(128) .
XACT_ABORT ON 应使用,以便在出现错误时正确发生回滚。
你可以 选择 直接脱离动态SQL,您不需要 OUT 参数,除非在静态端之后需要该值。

DECLARE @SQL NVARCHAR(MAX),
        @SchemaName sysname = 'dim',
        @TableName sysname = 'Sales',
        @NaturalKeyColumn sysname = 'SalesID',
        @NaturalKeyValue VARCHAR(40) = '100',
        @SurrogateKeyColumn sysname = 'SalesKey';

SET @SQL = N'
DECLARE @SurrogateKey INT;

SET XACT_ABORT, NOCOUNT ON;

BEGIN TRAN;

SELECT @SurrogateKey = ' + QUOTENAME(@SurrogateKeyColumn) + '
FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' WITH (SERIALIZABLE, UPDLOCK)
WHERE ' + QUOTENAME(@NaturalKeyColumn) + ' = @NaturalKeyValue;

IF @SurrogateKey IS NULL
BEGIN
    INSERT INTO ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' (' + QUOTENAME(@NaturalKeyColumn) + ', InferredFlag)
    VALUES (@NaturalKeyValue, 1);

    SET @SurrogateKey = SCOPE_IDENTITY();
END;

COMMIT;

SELECT @SurrogateKey;
';

EXEC sp_executesql @SQL,
  N'@NaturalKeyValue VARCHAR(40)',
    @NaturalKeyValue = @NaturalKeyValue;