sp_executesql Not Returning Correct Value Using OUTPUT

The code with 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 columns in the [contact_compare_fields] from two different tables.

The code currently returns in @h_data the column 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

Solution

It looks like you are attempting to use dynamic SQL to one of multiple columns based on the selected h_field value from your contact_compare_fields table. You cannot do this with a passed parameter. You must inject the column name directly into the SQL statement:

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

To protect against SQL injection and object names containing spaces or special characters, QUOTENAME() must be used to safely inject names or text values into dynamic SQL. However, your source data already has quoted names in the contact_compare_fields table, so the above code first removes the quoting characters [] before reapplying them using QUOTENAME(). A better approach would be to remove the []s from your contact_compare_fields table.

This will produce SQL that looks like:

    SELECT @h_dataOUT = SDU_Tools.NULLifBlank([Job Title])
    FROM [h_processed]
    WHERE CAST(CAST([Contact ID] AS FLOAT) AS BIGINT) = @ziIN;

When executed, this should produce your desired output: @h_data = 'Chief Financial Officer' instead of just @h_data = '[Job Title]'.

Note that the @h_fieldIN parameter is no longer used and can be dropped as a parameter in the EXEC sp_executesql ... statement.

See this db<>fiddle for a demo

I have not addressed the issues with excessive data conversions. In short, if your ID values are numbers (integers), you should store them using an appropriate type, like BIGINT and consistently treat them as numbers. If your ID values are fixed format text (even if the look like numbers) you should store them as text and consistently treat them as text - don't convert them to numbers for comparisons.