title: QUOTENAME is causing conversion errors in SQL?
body: <p>EDIT:</p>
  
  <p>ok this is really throwing me off now, I have the query working twice and then for no reason it stops working again and give me different errors each time. Now I am getting this error:</p>
  
  <pre><code>Msg 8115, Level 16, State 8, Procedure gsp_rpt_Custom_Extract_Haven_test_2, Line 12
  Arithmetic overflow error converting varchar to data type numeric.
  </code></pre>
  
  <p>It is so inconsistent, I can take out certain joins and the query runs but those join are completely irrelevant to the error and work absolutly fine independantly. Seriously confused about this one.</p>
  
  <p>END EDIT</p>
  
  <p>I have this query, its a fairly lengthy one. I am using the query for a BCP command which is why it looks odd. I am trying to add a text qualifier (as BCP cannot do it) by using quotename(), the query runs fine without the quote name for some reason I keep getting this error: </p>
  
  <pre><code>Msg 245, Level 16, State 1, Procedure gsp_rpt_Custom_Extract_test, Line 12
  Conversion failed when converting the varchar value '05-01-2015 12:49:05' to data type smallint.
  </code></pre>
  
  <p>As you can see for the query i have commented out the columns I have not got to yet as a part of my diagnosis. All the columns work ok up until the last one (the case statement) is whats causing the problem. But like I said above, the querty runs fine when I remove all the quotenames.</p>
  
  <p>Any help would be much appreciated.</p>
  
  <p>Thanks</p>
  
  <p>Full code:</p>
  
  <pre><code>WITH QUEST_CTE AS (
  
  SELECT CompanyID AS Company_ID, Q.QuestionID, QuestionText, CAST(Q.Answer AS VARCHAR) AS Answer
  FROM Questionnaire AS Q
  JOIN CallCentre_Web.dbo.Questionnaire_Questions QQ
  ON Q.QuestionID = QQ.QuestionID
  JOIN dbo.ProfileMapping AS PM
  ON PM.ProfileMappingID = Q.ProfileMappingID
  JOIN CampaignMapping AS CM
  ON PM.MapID = CM.MapID
  WHERE QuestionTypeID = 0
  AND QQ.GoldenField = 1
  AND CampaignID = 18
  UNION ALL
  
  SELECT CompanyID AS Company_ID, Q.QuestionID, QuestionText, CAST(QA.Answer AS VARCHAR) 
  FROM Questionnaire AS Q
  JOIN CallCentre_Web.dbo.Questionnaire_Questions QQ
  ON Q.QuestionID = QQ.QuestionID
  JOIN CallCentre_Web.dbo.Questionnaire_Answers QA
  ON Q.Answer = QA.AnswerID
  JOIN dbo.ProfileMapping AS PM
  ON PM.ProfileMappingID = Q.ProfileMappingID
  JOIN CampaignMapping AS CM
  ON PM.MapID = CM.MapID
  WHERE QuestionTypeID = 1
  AND QQ.GoldenField = 1
  AND CampaignID = 18
  UNION ALL
  
  SELECT CompanyID AS Company_ID, Q.QuestionID, QuestionText,
          CAST(STUFF((select ',' + e.Answer
          FROM Questionnaire a
          cross apply dbo.SplitString(a.Answer,',') b 
          join CallCentre_Web.dbo.Questionnaire_Answers e 
          on b.s = e.AnswerID
          where isnumeric(b.s) = 1 
          and QuestionTypeID = 2 
          and a.QuestionnaireID = q.QuestionnaireID
          for xml path('')),1,1,'') as Varchar) as Answer
  FROM Questionnaire AS q
  JOIN dbo.ProfileMapping AS PM
  ON PM.ProfileMappingID = Q.ProfileMappingID
  JOIN CampaignMapping AS CM
  ON PM.MapID = CM.MapID
  JOIN CallCentre_Web.dbo.Questionnaire_Questions QQ
  ON Q.QuestionID = QQ.QuestionID
  WHERE QuestionTypeID = 2
  AND QQ.GoldenField = 1
  AND CampaignID = 18
  )
  
  select CAST(Quotename('GS Id',char(34))as Varchar(255)),
  CAST(Quotename('CompanyName', char(34))as Varchar(255)),
  CAST(Quotename('Address', char(34))as Varchar(255)),
  CAST(Quotename('Country',  char(34))as Varchar(255)),
  CAST(Quotename('SicDescription',  char(34))as Varchar(255)),
  CAST(Quotename('Sector', char(34))as Varchar(255)),
  CAST(Quotename('Employees',  char(34))as Varchar(255)),
  CAST(Quotename('Website', char(34))as Varchar(255)),
  CAST(Quotename('CompanyTurnover', char(34))as Varchar(255)),
  CAST(Quotename('Phone',  char(34))as Varchar(255)),
  CAST(Quotename('MobilePhone', char(34))as Varchar(255)),
  CAST(Quotename('Fax',  char(34))as Varchar(255)),
  CAST(Quotename('Salutation',  char(34))as Varchar(255)),
  CAST(Quotename('Forename',  char(34))as Varchar(255)),
  CAST(Quotename('Surname',  char(34))as Varchar(255)),
  CAST(Quotename('Jobtitle', char(34))as Varchar(255)),
  CAST(Quotename('EmailAddress', char(34))as Varchar(255)),
  CAST(Quotename('LeadSource', char(34))as Varchar(255))
  --CAST(Quotename('CallResult', char(34))as Varchar(255)),
  --CAST(Quotename('What is your company registration number?', char(34))as Varchar(255))
  --CAST(Quotename('Who is your electricity supplier?', char(34))as Varchar(255)),
  --CAST(Quotename('Number of sites', char(34))as Varchar(255)),
  --CAST(Quotename('MPAN Number', char(34))as Varchar(255)),
  --CAST(Quotename('Do you use Half Hourly Meters?', char(34))as Varchar(255)),
  --CAST(Quotename('What is your average use for electricity? (in GWh)', char(34))as Varchar(255)),
  --CAST(Quotename('Do you use a fixed or flexible contract for your electricity? (if over 20GWh)', char(34))as Varchar(255)),
  --CAST(Quotename('What is your average spend on electricity?', char(34))as Varchar(255)),
  --CAST(Quotename('Do you have any involvement with a TPI?', char(34))as Varchar(255)),
  --CAST(Quotename('If so, who is your TPI Supplier?',char(34))as Varchar(255)),
  --CAST(Quotename('Electricity Contract Expiry Date',char(34)) as Varchar(255))
  
  union all
  
  
  select
  Quotename(cast(C.CompanyID as varchar(255)),char(34)),  
  Quotename(CompanyName, char(34)),
  Quotename(ISNULL(AddressLine1,'') + case when addressline1 is null then '' else ', ' end + ISNULL(AddressLine2,'') + case when addressline2 is null then '' else ', ' end + ISNULL(AddressLine3,'') + case when addressline3 is null then '' else ', ' end + ISNULL(Town,'') + case when town is null then '' else ', ' end + ISNULL(County,'') + case when county is null then '' else ', ' end + ISNULL(Region,'') + case when region is null then '' else ', ' end + Postcode, char(34)),
  Quotename('UK', char(34)),
  Quotename(SIC.SicDescription, char(34)),
  Quotename(sec.Sector, char(34)),
  cast(quotename(ISNULL(NumberOfEmployees,''),char(34)) as varchar(255)),
  cast(Quotename(ISNULL(Website,''),char(34)) as Varchar(255)),
  cast(Quotename(ISNULL(CompanyTurnover,''),char(34)) as varchar(255)),
  Quotename(ISNULL(MainTel.Phonenumber,''),char(34)) ,
  Quotename(ISNULL(MobTel.PhoneNumber,''),char(34)),
  Quotename(ISNULL(FaxTel.PhoneNumber,''), char(34)),
  Quotename(ISNULL(Salutation,''), char(34)),
  Quotename(ISNULL(Forename,''), char(34)),
  Quotename(ISNULL(Surname,''), char(34)),
  Quotename(ISNULL(Jobtitle,''),char(34)),
  Quotename(ISNULL(EmailAddress,''),char(34)),
  CASE  WHEN [What is your average use for electricity? (in GWh)] &gt;= 1.5 OR [What is your average spend on electricity?] &gt;= 150000 THEN quotename('GS1',char(34))
        WHEN [What is your average use for electricity? (in GWh)] &lt; 1.5 OR [What is your average spend on electricity?] &lt; 150000 THEN quotename('GS1 SME',char(34))
        ELSE quotename('GS1',char(34)) END
  --Quotename(CallResult,char(34))
  --Quotename(ISNULL([What is your company registration number?],''),char(34))
  --CASE WHEN ISNULL([Who is your electricity supplier?],'') = 'Other' THEN ISNULL([If other please state who (electric)],'')
  --ELSE ISNULL([Who is your electricity supplier?],'') END,
  --Quotename(ISNULL([Number of sites],''),char(34))
  --ISNULL([MPAN Number],''),
  --ISNULL([Do you use Half Hourly Meters?],'') ,
  --ISNULL([What is your average use for electricity? (in GWh)],''),
  --ISNULL([Do you use a fixed or flexible contract for your electricity? (if over 20GWh)],''),
  --ISNULL([What is your average spend on electricity?],''),
  --ISNULL([Do you have any involvement with a TPI?],''),
  --ISNULL([If so, who is your TPI Supplier?],''),
  --CAST(ISNULL(replace(convert(Varchar(10),[Electricity Contract Expiry Date],103),'-','/'),'') AS Varchar(20))
  from company c
  join callcentre_web.dbo.LK_SIC as sic
  on sic.sicid = c.sicid
  and sic.sictype = c.sictype
  join callcentre_web.dbo.LK_Sector as sec
  on sec.sectorid = sic.sectorid
  and sic.sictype = sec.SectorType
  left join ( 
              SELECT * FROM (
              select row_number() over (partition by companyID order by C.CompanyID) as ROW, 
              CompanyID, Surname , Forename , s.salutation, Emailaddress, Jobtitle
              from contacts as C
              join callcentre_web.dbo.lk_Salutation as s
              on c.salutation = s.salutationid
              join contactdetails as cd 
              on cd.contactid = c.contactid
              and campaignid = 18
              where responsibilityID = 3 /* PRIMARY CONTACT */
              ) x
              WHERE ROW = 1
             ) as Con
  on Con.CompanyID = c.CompanyID
  left join (
              SELECT * FROM (
              select row_number() over (partition by cn.companyID order by Cn.CompanyID) as ROW,
              cn.companyid, PhoneNumber
              from contactnumbers cn
              join company as c
              on c.companyid = cn.companyid
              join campaignmapping as cm
              on cm.companyid = c.companyid
              where phonetypeid not in (3,4,5)--Mobile, TPS, Fax
              and campaignid = 18
              ) x
              where row = 1
              ) AS MainTel
  ON MainTel.companyid = C.companyID
  left join (
              SELECT * FROM (
              select row_number() over (partition by cn.companyID order by Cn.CompanyID) as ROW,
              cn.companyid, PhoneNumber
              from contactnumbers cn
              join company as c
              on c.companyid = cn.companyid
              join campaignmapping as cm
              on cm.companyid = c.companyid
              where phonetypeid = 3--Mobile
              and PhoneNumber like '07%'
              and campaignid = 18
              ) x
              where row = 1
              ) AS MobTel
  ON MobTel.companyid = C.companyID
  left join (
              SELECT * FROM (
              select row_number() over (partition by cn.companyID order by Cn.CompanyID) as ROW,
              cn.companyid, PhoneNumber
              from contactnumbers cn
              join company as c
              on c.companyid = cn.companyid
              join campaignmapping as cm
              on cm.companyid = c.companyid
              where phonetypeid = 5--Fax
              and campaignid = 18
              ) x
              where row = 1
              ) AS FaxTel
  ON FaxTel.companyid = C.companyID
  join campaignmapping as cm
  on cm.companyid = c.companyid
  and CampaignID = 18
  join calldetails as cd
  on cd.mapid = cm.mapid
  left join callcentre_web..lk_callresults as lkc
  on lkc.callresultid = cd.callresultid
  left join ( SELECT * FROM
                          (
                              SELECT Company_ID, QuestionText, Answer
                              FROM Quest_CTE
                          ) AS source
  PIVOT
  (
      MAX(Answer)
      FOR QuestionText IN ([What is your company registration number?],
  [Who is your electricity supplier?],[Number of sites],[If other please state who (electric)],
  [MPAN Number],[Do you use Half Hourly Meters?],
  [Do you use a fixed or flexible contract for your gas? (if over 20GWh)],
  [What is your average use for electricity? (in GWh)],
  [Do you use a fixed or flexible contract for your electricity? (if over 20GWh)],
  [What is your average spend on Gas?],[What is your average spend on electricity?],
  [Do you have any involvement with a TPI?],[If so, who is your TPI Supplier?],
  [Gas Contract expiry date],[Electricity Contract Expiry Date])
  ) as pvt
  ) AS PV 
  ON PV.Company_ID = C.CompanyID
  </code></pre>