SCOPE_IDENTITY() is returning NULL instead of IDENTITY value - T-SQL

I'm using dynamic SQL to insert a default value into a table and want to return the SCOPE_IDENTITY() value for my surrogate key. However, SCOPE_IDENTITY() is returning NULL for some reason. Am I missing something?

Thanks!

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;

SCOPE_IDENTITY() unsurprisingly only works within a single scope. You need to create and execute a single dynamic SQL batch. This is also much more efficient.

Other issues with your code:

• The transaction is being started in the wrong place, it needs to be before the SELECT.
• You need to add locking hints to the SELECT, otherwise a shared lock is taken which won't help you. You need SERIALIZABLE and UPDLOCK here.
• Object and column names should be stored in sysname variables, which is an alias for nvarchar(128).
• XACT_ABORT ON should be used so that rollbacks happen correctly in case of errors.
• You can SELECT directly out of dynamic SQL, you don't need the OUT parameter unless you need the value afterwards in the static side.

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;