I usually don’t use parameters for the sp_executesql, but I am trying to be better. I think I do not have something set up correctly for this one.

The code with the sp_executesql is in a loop (two actually), that goes through the list of IDs in the [tmp_compare], and compares the data in each of the fields in the [contact_compare_fields] from two different tables.

The code currently returns in @h_data the field name [Title] instead of the data that is in the table ie ‘Chief Financial Officer’

The SQL prints out as:
SELECT @h_dataOUT = SDU_Tools.NULLifBlank(@h_fieldIN) FROM [h_processed] WHERE CAST(CAST([Contact ID] AS FLOAT) AS BIGINT) = @ziIN;

And if I remove the @h_dataOUT and replace the @h_fieldIN with [Job Title], and @ziIN with the ID to look like this:
SELECT SDU_Tools.NULLifBlank([Job Title]) FROM [h_processed] WHERE CAST(CAST([Contact ID] AS FLOAT) AS BIGINT) = 1001122877
Then it returns the value I am expecting.

Right now I have two sets of these, one for the sf_data, and one for the h_data. They both return the incorrect value right now. The whole process runs REALLY slow (ie 6hr+ for 120k times through the loop), so I am hoping that by fixing this it will speed it up.

CREATE TABLE [dbo].[contact_compare_fields] (
    [id]        int     identity(1,1),
    [sf_field]  varchar(100),
    [h_field]   varchar(100)
);

INSERT INTO [contact_compare_fields] VALUES
    ('[Title]', '[Job Title]'),
    ('[FirstName]', '[First Name]'),
    ('[LastName]', '[Last Name]'),
    ('[MailingStreet]', '[Person Street]'),
    ('[MailingCity]', '[Person City]'),
    ('[MailingState]', '[Person State]'),
    ('[MailingPostalCode]', '[Person Zip Code]'),
    ('[MailingCountry]', '[Country]')
    
CREATE TABLE [dbo].[tmp_compare] (
    [id]        int     identity(1,1),
    [H_ZI_ID]   bigint
)

INSERT INTO [tmp_compare] VALUES
    ('1001122877'),('1001125385'),('1002260105'),('100233801'),('1002661679')

CREATE TABLE [dbo].[h_processed] (
    [Contact ID]    varchar(255),
    [Job Title]         varchar(255)
)

INSERT INTO [h_processed] VALUES ('1001122877', 'Chief Financial Officer')



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)
    

     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 @sql = N'SELECT @h_dataOUT = SDU_Tools.NULLifBlank(@h_fieldIN) FROM [h_processed] WHERE CAST(CAST([Contact ID] AS FLOAT) AS BIGINT) = @ziIN;';
     SET @ParamDef = N'@h_fieldIN nvarchar(500), @ziIN nvarchar(50), @h_dataOUT varchar(500) OUTPUT';
     EXEC sp_executesql @sql, @ParamDef, @h_fieldIN = @h_field, @ziIN = @zi, @h_dataOUT = @h_data OUTPUT;
PRINT 'h_data:  ' + @h_data