SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

14 rows affected

DECLARE
    @zi nvarchar(50) = '',
    @sf_field nvarchar(500) = '',
    @h_field nvarchar(500) = '',
    @sf_data nvarchar(500) = '',
    @h_data nvarchar(500) = '',
    @ParamDef nvarchar(500) = '',
    @sql nvarchar(max)

DECLARE @fld_cnt INT = 1 -- Added (missing from original post)
DECLARE @cnt INT = 1     -- Added (missing from original post)
  
SELECT
    @sf_field = [sf_field],
    @h_field = [h_field]
FROM [contact_compare_fields]
WHERE [id] = @fld_cnt

SELECT @zi = [H_ZI_ID]
FROM [tmp_compare]
WHERE [id] = @cnt

SET @h_field = REPLACE(REPLACE(@h_field, '[', ''), ']', '') -- unquote
SET @sql = N'
    --SELECT @h_dataOUT = SDU_Tools.NULLifBlank(@h_fieldIN)
    --SELECT @h_dataOUT = SDU_Tools.NULLifBlank(' + QUOTENAME(@h_field) + ', '''')
    SELECT @h_dataOUT = NULLIF(' + QUOTENAME(@h_field) + ', '''')
    FROM [h_processed]
    WHERE CAST(CAST([Contact ID] AS FLOAT) AS BIGINT) = @ziIN;
';

PRINT 'zi:  ' + @zi;
PRINT 'h_field:  ' + @h_field;
PRINT 'sql:  ' + @sql;
  
SET @ParamDef = N'
    @ziIN nvarchar(50),
    @h_dataOUT varchar(500) OUTPUT
';

EXEC sp_executesql
    @sql,
    @ParamDef,
    @ziIN = @zi,
    @h_dataOUT = @h_data OUTPUT;

PRINT 'h_data:  ' + @h_data;

zi:  1001122877
h_field:  Job Title
sql:  
    --SELECT @h_dataOUT = SDU_Tools.NULLifBlank(@h_fieldIN)
    --SELECT @h_dataOUT = SDU_Tools.NULLifBlank([Job Title], '')
    SELECT @h_dataOUT = NULLIF([Job Title], '')
    FROM [h_processed]
    WHERE CAST(CAST([Contact ID] AS FLOAT) AS BIGINT) = @ziIN;

h_data:  Chief Financial Officer