SCOPE_IDENTITY() is Returning NULL instead of IDENTITY Value

Hello,

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 for your assistance.

BEGIN
    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;
END