Error: Conversion failed when converting the nvarchar value ' ' to data type int RSS

• • Reply
  

  Triwis

  Member

  301 Points

  150 Posts

  Error: Conversion failed when converting the nvarchar value ' ' to data type int

  Mar 28, 2011 03:22 PM|LINK

  Hi,

  I have a query in SQL Server 2008:

   

  DECLARE @clt_ID INT = 34;
  DECLARE @Query NVARCHAR(4000);
  
  SET @Query = 'SELECT ctr.ctr_ID,
                clt.clt_ID, clt.clt_Nom,
  	      sit1.sit_ID, sit1.sit_Adresse, sit1.sit_Adresse2, sit1.sit_Ville, sit1.sit_Prov, sit1.sit_Pays, sit1.sit_Postal, sit1.sit_Distance,
  	      sit2.sit_ID, sit2.sit_Adresse, sit2.sit_Adresse2, sit2.sit_Ville, sit2.sit_Prov, sit2.sit_Pays, sit2.sit_Postal, sit2.sit_Distance,
  	      ctr.ctr_Datedebut, ctr.ctr_Datefin,
                emp.emp_ID, emp.emp_Nom,
  	      cnt1.cnt_ID, cnt1.cnt_Nom,
  	      cnt2.cnt_ID, cnt2.cnt_Nom,
  	      cnt3.cnt_ID, cnt3.cnt_Nom,
  	      ctg1.ctg_ID, ctg1.ctg_Desc,
  	      ctg2.ctg_ID, ctg2.ctg_Desc,
  	      ctg3.ctg_ID, ctg3.ctg_Desc,
  	      ctr.ctr_Escompte, ctr.ctr_Montant,
  	      frn.frn_ID, frn.frn_Nom,
  	      ctr_EstActif
  	    	  
                FROM dbo.TB_Contrat ctr
  	      INNER JOIN dbo.TB_Client clt ON ctr.clt_ID = clt.clt_ID
  	      INNER JOIN dbo.TB_Site sit1 ON ctr.sit_ID_Liv = sit1.sit_ID
  	      INNER JOIN dbo.TB_Site sit2 ON ctr.sit_ID_Fact = sit2.sit_ID
  	      INNER JOIN dbo.TB_Employe emp ON ctr.emp_ID = emp.emp_ID
  	      INNER JOIN dbo.TB_Contact cnt1 ON ctr.ctr_AutoriseID = cnt1.cnt_ID
                INNER JOIN dbo.TB_Contact cnt2 ON ctr.cnt_ID = cnt2.cnt_ID
                INNER JOIN dbo.TB_Contact cnt3 ON ctr.ctr_ChargeMtnID = cnt3.cnt_ID
  	      INNER JOIN dbo.TB_Constante ctg1 ON ctr.ctr_TypeID = ctg1.ctg_ID
                INNER JOIN dbo.TB_Constante ctg2 ON ctr.ctr_statutID = ctg2.ctg_ID
                INNER JOIN dbo.TB_Constante ctg3 ON ctr.ctr_paiementID = ctg3.ctg_ID
                INNER JOIN dbo.TB_Fournisseur frn ON ctr.frn_ID = frn.frn_ID
                '
  SET @Query = @Query + 'WHERE ctr.clt_ID = ' + @clt_ID
  EXEC sp_ExecuteSQL @Query;

   

  Everything looks fine but I keep getting the error: 'Conversion failed when converting the nvarchar value 'xxxx' to data type int... where xxxx is the the value of @Query.

  If anyone can figure out what I am doing wrong, please let me know.

  Thanks

   

  • Edited by Triwis on Mar 28, 2011 03:26 PM
• • Reply
  

  TabAlleman

  All-Star

  15557 Points

  2698 Posts

  Re: Error: Conversion failed when converting the nvarchar value ' ' to data type int

  Mar 28, 2011 03:29 PM|LINK

  Triwis

  SET @Query = @Query + 'WHERE ctr.clt_ID = ' + @clt_ID

  Change to:

  SET @Query = @Query + 'WHERE ctr.clt_ID = ' + CAST(@clt_ID AS nvarchar(15))

  -Tab Alleman
  • Marked as answer by Triwis on Mar 28, 2011 03:31 PM
• • Reply
  

  limno

  All-Star

  117314 Points

  7997 Posts

  Moderator

  MVP

  Re: Error: Conversion failed when converting the nvarchar value ' ' to data type int

  Mar 28, 2011 03:29 PM|LINK

  Try this query without using dynamic query to see what error you may have?

   

  DECLARE @clt_ID INT = 34
  
  SELECT ctr.ctr_ID,clt.clt_ID,clt.clt_Nom,sit1.sit_ID,sit1.sit_Adresse,
  sit1.sit_Adresse2,sit1.sit_Ville,sit1.sit_Prov,sit1.sit_Pays,
  sit1.sit_Postal,sit1.sit_Distance,sit2.sit_ID,sit2.sit_Adresse,
  sit2.sit_Adresse2,sit2.sit_Ville,sit2.sit_Prov,sit2.sit_Pays,
  sit2.sit_Postal,sit2.sit_Distance,ctr.ctr_Datedebut,ctr.ctr_Datefin,
  emp.emp_ID,emp.emp_Nom,cnt1.cnt_ID,cnt1.cnt_Nom,cnt2.cnt_ID,cnt2.cnt_Nom,
  cnt3.cnt_ID,cnt3.cnt_Nom,ctg1.ctg_ID,ctg1.ctg_Desc,ctg2.ctg_ID,ctg2.ctg_Desc,
  ctg3.ctg_ID,ctg3.ctg_Desc,ctr.ctr_Escompte,ctr.ctr_Montant,frn.frn_ID,
  frn.frn_Nom,ctr_EstActif
  
  FROM   dbo.TB_Contrat ctr
         INNER JOIN dbo.TB_Client clt
           ON ctr.clt_ID = clt.clt_ID
         INNER JOIN dbo.TB_Site sit1
           ON ctr.sit_ID_Liv = sit1.sit_ID
         INNER JOIN dbo.TB_Site sit2
           ON ctr.sit_ID_Fact = sit2.sit_ID
         INNER JOIN dbo.TB_Employe emp
           ON ctr.emp_ID = emp.emp_ID
         INNER JOIN dbo.TB_Contact cnt1
           ON ctr.ctr_AutoriseID = cnt1.cnt_ID
         INNER JOIN dbo.TB_Contact cnt2
           ON ctr.cnt_ID = cnt2.cnt_ID
         INNER JOIN dbo.TB_Contact cnt3
           ON ctr.ctr_ChargeMtnID = cnt3.cnt_ID
         INNER JOIN dbo.TB_Constante ctg1
           ON ctr.ctr_TypeID = ctg1.ctg_ID
         INNER JOIN dbo.TB_Constante ctg2
           ON ctr.ctr_statutID = ctg2.ctg_ID
         INNER JOIN dbo.TB_Constante ctg3
           ON ctr.ctr_paiementID = ctg3.ctg_ID
         INNER JOIN dbo.TB_Fournisseur frn
           ON ctr.frn_ID = frn.frn_ID
  WHERE  ctr.clt_ID = @clt_ID

  Limno
  
  Format your SQL query with instant sql formatter:
  http://www.dpriver.com/pp/sqlformat.htm
• • Reply
  

  Triwis

  Member

  301 Points

  150 Posts

  Re: Error: Conversion failed when converting the nvarchar value ' ' to data type int

  Mar 28, 2011 03:32 PM|LINK

  TabAlleman

  Change to:

  SET @Query = @Query + 'WHERE ctr.clt_ID = ' + CAST(@clt_ID AS nvarchar(15))

  Working perfectly!

  Thanks a lot