title: Conversion failed when converting the varchar value '"' to datatype int in dynamic query
body: <p>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:</p>
  
  <pre><code>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');
  </code></pre>
  
  <p>And the query I am attempting to run is as follows:</p>
  
  <pre><code>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) &gt; 1 AND Len(@InsertQuery) &gt; 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
  </code></pre>
  
  <p>The problem seems to stem from trying to select on record_id, which gives the <em>'Conversion failed when converting the varchar value '"' to datatype int'</em> error</p>
  
  <p>If I do not include the record_id column (which is the only INT column in the select list) it seems to work ok.</p>
  
  <p>I have tried to apply CONVERT(VARCHAR(250), record_id) but cannot seem to get the syntax correct.</p>
  
  <p>Any help would be greatly appreciated</p>