Conversion failed when converting the varchar value '"' to datatype int in dynamic query

Question

I am receiving the above error when I try to execute a query on MS SQL Server 2005. It is a dynamic query built up in multiple parts. Here is the simplified structure and data:

CREATE TABLE [record_fields](
[field_id] [int] NOT NULL,
[campaign_id] [int] NOT NULL,
[record_import_type_id] [int] NOT NULL,
[fieldname] [varchar](150) NOT NULL,
[import_file_column_index] [smallint] NOT NULL,
[records_fieldname] [varchar](50) NOT NULL,
[show_field] [bit] NOT NULL,
[field_order] [int] NOT NULL,
[dialler_field_required] [bit] NULL,
[dialler_field_name] [varchar](50) NULL,
[dialler_field_order] [int] NULL,
[field_group_id] [int] NOT NULL
);     

INSERT INTO [record_fields] VALUES(1,2,1,'Record Id',47,'record_id',0,1,1,'Record Id',NULL,1);
INSERT INTO [record_fields] VALUES(2,2,1,'Field Name 1',46,'field01',0,1,1,'Field 1',NULL,1);
INSERT INTO [record_fields] VALUES(3,2,1,'Field Name 2',46,'field02',0,1,1,'Field 2',NULL,1);

CREATE TABLE [records](
[record_id] [int] NOT NULL,
[campaign_id] [int] NOT NULL,
[dialler_entry_created] BIT NOT NULL,
[field01] [VARCHAR](50) NULL,
[field02] [VARCHAR](50) NULL
);

INSERT INTO [records] VALUES(1,2,0,'Field01 Value','Field02 Value');
INSERT INTO [records] VALUES(1,2,0,'Field01 Value','Field02 Value');

And the query I am attempting to run is as follows:

DECLARE @campaignId INT
SET @campaignId = 2

DECLARE @FieldName VARCHAR(250)
DECLARE @ColumnName VARCHAR(250)
DECLARE @SelectQuery VARCHAR(2000)
DECLARE @InsertQuery VARCHAR(2000)

SET @SelectQuery = ''
SET @InsertQuery = ''

declare #FieldNames cursor for SELECT records_fieldname, dialler_field_name FROM record_fields where campaign_id = @campaignid AND dialler_field_required = 1 ORDER BY dialler_field_order
        open #FieldNames
        fetch next from #FieldNames into @FieldName, @ColumnName
        while @@fetch_status = 0
        begin
            -- Build up a dymamic string of columns to read in the select query
            SET @SelectQuery = @SelectQuery + '''"''+' + @FieldName + '+''"'', '
            -- Build up a dynamic string of column names to add to our temp table
            SET @InsertQuery = @InsertQuery + '[' + @ColumnName + '] varchar(255), '

    fetch next from #FieldNames into @FieldName, @ColumnName
    end
    close #FieldNames
    deallocate #FieldNames

    IF Len(@SelectQuery) > 1 AND Len(@InsertQuery) > 1
    BEGIN
        -- Trim the trailing ','
        SET @InsertQuery = Left(@InsertQuery,Len(@InsertQuery)-1)
        SET @SelectQuery = Left(@SelectQuery,Len(@SelectQuery)-1)

        EXEC ('DECLARE @RecordData TABLE (' + @InsertQuery + ');'
                + 'INSERT INTO @RecordData SELECT ' + @SelectQuery + ' from records WHERE campaign_id =' + @campaignId + ' AND ISNULL(dialler_entry_created, 0) = 0; '
                + 'SELECT * FROM @RecordData;')
    END

The problem seems to stem from trying to select on record_id, which gives the 'Conversion failed when converting the varchar value '"' to datatype int' error

If I do not include the record_id column (which is the only INT column in the select list) it seems to work ok.

I have tried to apply CONVERT(VARCHAR(250), record_id) but cannot seem to get the syntax correct.

Any help would be greatly appreciated

You need another parenthesis: `CONVERT(VARCHAR(250), record_id)` — NickyvV, Apr 22 '15 at 11:06
The missing parenthesis is just a typo. I'll fix it in the question. I have also tried printing the InsertQuery and SelectQuery, but I cannot spot the issue. — Yetiish, Apr 22 '15 at 11:25

score 1 · Accepted Answer · answered Apr 22 '15 at 11:44

There were 2 errors. In constructing select query and campaignid in exec function:

DECLARE @campaignId INT
SET @campaignId = 2

DECLARE @FieldName VARCHAR(250)
DECLARE @ColumnName VARCHAR(250)
DECLARE @SelectQuery VARCHAR(2000)
DECLARE @InsertQuery VARCHAR(2000)

SET @SelectQuery = ''
SET @InsertQuery = ''

declare #FieldNames cursor for SELECT records_fieldname, dialler_field_name FROM record_fields where campaign_id = @campaignid AND dialler_field_required = 1 ORDER BY dialler_field_order
        open #FieldNames
        fetch next from #FieldNames into @FieldName, @ColumnName
        while @@fetch_status = 0
        begin
            -- Build up a dymamic string of columns to read in the select query
            SET @SelectQuery = @SelectQuery + @FieldName + ', '            
            -- Build up a dynamic string of column names to add to our temp table
            SET @InsertQuery = @InsertQuery + '[' + @ColumnName + '] varchar(255), '

    fetch next from #FieldNames into @FieldName, @ColumnName
    end
    close #FieldNames
    deallocate #FieldNames

    IF Len(@SelectQuery) > 1 AND Len(@InsertQuery) > 1
    BEGIN
        -- Trim the trailing ','
        SET @InsertQuery = Left(@InsertQuery,Len(@InsertQuery)-1)
        SET @SelectQuery = Left(@SelectQuery,Len(@SelectQuery)-1)
        Declare @result nvarchar(max) ='DECLARE @RecordData TABLE (' + @InsertQuery + ');'
                + 'INSERT INTO @RecordData SELECT ' + @SelectQuery + ' from records WHERE campaign_id =' + cast(@campaignId as nvarchar(50))+ ' AND ISNULL(dialler_entry_created, 0) = 0; '
                + 'SELECT * FROM @RecordData;'
        Exec(@result)                    
    END

Here is working fiddle: http://sqlfiddle.com/#!6/e450c/23

Seems the inclusion of ''"'' was overcomplicating things! Seems to work now, thanks! — Yetiish, Apr 22 '15 at 11:59

score 0 · Answer 2 · answered Apr 22 '15 at 11:12

0

I think this is your problem:

DECLARE @campaignId INT
SET @campaignId = 2
. . .
+ 'INSERT INTO @RecordData SELECT ' + @SelectQuery + ' from records WHERE campaign_id =' + @campaignId + ' AND ISNULL(dialler_entry_created, 0) = 0; '

Notice the + @campaignId.

Try this instead:

DECLARE @campaignId varchar(255);
SET @campaignId = '2';

answered Apr 22 '15 at 11:12

Gordon Linoff

1,242,037
58
646
786

Thanks I tried it, but no luck. The problem only occurs when I include record_id column in the SelectQuery, it works if I exclude this column, so I think it must be related to this, not the campaign_id parameter – Yetiish Apr 22 '15 at 11:24

score 0 · Answer 3 · answered Apr 22 '15 at 12:04

        SET @SelectQuery = @SelectQuery + '''"''+' + @FieldName + '+''"'', '

Change to

        SET @SelectQuery = @SelectQuery + ' ' + @FieldName + ' ,'

If you want an often discouraged (but nonetheless very handy) shortcut to converting numbers to string, change the bit at the bottom from

@campaignId

to

RTRIM@campaignId)

Does the character conversion and trims off trailing etc all in one word.

Conversion failed when converting the varchar value '"' to datatype int in dynamic query

3 Answers3