Início / coding / Perguntas / 79304153
Accepted
libpekin1847
Asked: 2024-12-24 04:28:44 +0800 CST

SCOPE_IDENTITY() está retornando NULL em vez do valor IDENTITY - T-SQL

  • 772

Estou usando SQL dinâmico para inserir um valor padrão em uma tabela e quero retornar o SCOPE_IDENTITY()valor para minha surrogate key. No entanto, SCOPE_IDENTITY()está retornando NULLpor algum motivo. Estou esquecendo de algo?

Obrigado!

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;
sql-server

1 respostas

  1. Best Answer

    SCOPE_IDENTITY()sem surpresas só funciona dentro de um único escopo. Você precisa criar e executar um único lote SQL dinâmico. Isso também é muito mais eficiente.

    Outros problemas com seu código:

    • A transação está sendo iniciada no lugar errado, precisa ser antes do SELECT.
    • Você precisa adicionar dicas de bloqueio ao SELECT, caso contrário, um bloqueio compartilhado é obtido, o que não vai ajudar você. Você precisa de SERIALIZABLEe UPDLOCKaqui.
    • Nomes de objetos e colunas devem ser armazenados em sysnamevariáveis, que é um alias para nvarchar(128).
    • XACT_ABORT ONdeve ser usado para que as reversões ocorram corretamente em caso de erros.
    • Você pode fazer isso SELECTdiretamente do SQL dinâmico. Não precisa do OUTparâmetro, a menos que precise do valor posteriormente no lado estático.
    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;
    • 2

relate perguntas