Conversion failed when converting the varchar value 'BB-10001' to data type int.

Question

Conversion failed when converting the varchar value 'BB-10001' to data type int.

Analyst_SQL 3,576

Below is data ,i am facing error

Create table #tbl_Bale_Prd (Bale_ID varchar(12),Item_Code int,Bale_Qty int ,Bale_Weight decimal(10,2),D_DID int)  
Create table #tbl_GRN_Detail (D_ID int,Item_Code int,Item_Qty int,Item_Weight decimal(10,2),D_DID int)  
  
  
Insert into #tbl_Bale_Prd values ('BB-10001',1,1,555,null)  
Insert into #tbl_Bale_Prd values ('BB-10002',2,1,410,null)  
Insert into #tbl_Bale_Prd values ('BB-10003',3,1,545,null)  
  
  
Insert into #tbl_GRN_Detail values (12001,1,1,555,null)  
Insert into #tbl_GRN_Detail values (12002,6,1,451,null)  
Insert into #tbl_GRN_Detail values (12003,8,1,980,null)  
  
  
  
select Bale_ID,item_code,Bale_Qty,Bale_Weight,D_DID From #tbl_Bale_Prd  where  Bale_ID = 'BB-10001' and (D_DID IS NULL or D_DID=1)  
  
union   
  
Select CONVERT(INT, CONVERT(VARCHAR(15), D_ID))  as Bale_ID,item_code,Item_Qty as Bale_Qty,Item_Weight as Bale_WEight,D_DID from #tbl_GRN_Detail    
where  D_ID = 'BB-10001' and (D_DID IS NULL or D_DID=1)

Desire Output

Yitzhak Khabinsky 26,586 Reputation points

2021-11-25T16:26:47.627+00:00

@Analyst_SQL ,

Please edit your question, and add a desired output.
Yitzhak Khabinsky 26,586 Reputation points

2021-11-25T17:42:33.473+00:00
@Analyst_SQL ,

A couple of questions:

Your desired output is not matching two input db tables. Please fix it.

Your logic is not clear. You are trying to CONVERT the D_ID column which is already INT data type to INT. PLease clarify your logic.
Analyst_SQL 3,576 Reputation points

2021-11-25T17:53:07.14+00:00

I want to convert int into varchar ,

Your logic is not clear. You are trying to CONVERT the D_ID column which is already INT data type to INT. PLease clarify your logic.

for what i am doing, I want to retrieve data from tables (#tbl_GRN_Detail and #tbl_Bale_Prd ) by search Bale_ID and insert into third table,but before inserting it will check from both table that where Bale_ID exist.

Problem is that in tbl_Bale_Prd table i have Bale_ID varchar type and in tbl_GRN_Detail table D_ID is int type,so that why i want to convert int Data type to varchar data type
Bruno CAVESTRO 1 Reputation point

2021-11-25T18:01:27.01+00:00

SO ... do you need to convert 12001 into varchar?
Is this your need?
Analyst_SQL 3,576 Reputation points

2021-11-25T18:05:31.237+00:00

yes,i want convert 12001 into varchar
EchoLiu-MSFT 14,621 Reputation points

2021-11-29T06:03:31.573+00:00

Could you please validate all the answers so far and provide any update?
If all of them are not working or helpful, please provide more sample data or details about your issue.
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

Accepted answer

2 additional answers

Your answer

Yitzhak Khabinsky 26,586 Reputation points

2021-11-25T16:26:47.627+00:00

@Analyst_SQL ,

Please edit your question, and add a desired output.
Yitzhak Khabinsky 26,586 Reputation points

2021-11-25T17:42:33.473+00:00

@Analyst_SQL ,

A couple of questions:

Your desired output is not matching two input db tables. Please fix it.

Your logic is not clear. You are trying to CONVERT the D_ID column which is already INT data type to INT. PLease clarify your logic.
Analyst_SQL 3,576 Reputation points

2021-11-25T17:53:07.14+00:00

I want to convert int into varchar ,

Your logic is not clear. You are trying to CONVERT the D_ID column which is already INT data type to INT. PLease clarify your logic.

for what i am doing, I want to retrieve data from tables (#tbl_GRN_Detail and #tbl_Bale_Prd ) by search Bale_ID and insert into third table,but before inserting it will check from both table that where Bale_ID exist.

Problem is that in tbl_Bale_Prd table i have Bale_ID varchar type and in tbl_GRN_Detail table D_ID is int type,so that why i want to convert int Data type to varchar data type
Bruno CAVESTRO 1 Reputation point

2021-11-25T18:01:27.01+00:00

SO ... do you need to convert 12001 into varchar?
Is this your need?
Analyst_SQL 3,576 Reputation points

2021-11-25T18:05:31.237+00:00

yes,i want convert 12001 into varchar
EchoLiu-MSFT 14,621 Reputation points

2021-11-29T06:03:31.573+00:00

Could you please validate all the answers so far and provide any update?
If all of them are not working or helpful, please provide more sample data or details about your issue.
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

Answer 1

Olaf Helper 47,436

 select Bale_ID, ...      
 union       
 Select CONVERT(INT, CONVERT(VARCHAR(15), D_ID))

Bale_ID is varchar, in the secon SELECT you convert a varchar to integer and because of Data type precedence (Transact-SQL) SQL Server now converts BaleID also to integer, what of course fails.
Remove the integer conversion.

Analyst_SQL 3,576 Reputation points

2021-11-30T08:45:10+00:00

that is not possible to handle bothe varchar and int
Olaf Helper 47,436 Reputation points

2021-11-30T08:49:16.317+00:00
that is not possible to handle bothe varchar and int

I don't understand what you mean? You just have to remove the integer conversion =>

select Bale_ID, ... union Select CONVERT(VARCHAR(15), D_ID) ...
Analyst_SQL 3,576 Reputation points

2021-12-02T07:08:43.403+00:00

what you mean ( You just have to remove the integer conversion =>)

I want both datatype (Varchar and Int) get pass
Olaf Helper 47,436 Reputation points

2021-12-02T07:14:01.167+00:00

I want both datatype (Varchar and Int) get pass

See my previous post, in a UNION select the column combination of query 1 & 2 have to return the same data types, otherwise the types get implict converted, which fails in your case.

Answer 2

Yitzhak Khabinsky 26,586

Hi @

Please try the following solution.

SQL

-- DDL and sample data population, start
 DECLARE @tbl_Bale_Prd TABLE (
    Bale_ID varchar(12),
    Item_Code int,
    Bale_Qty int,
    Bale_Weight decimal(10,2),
    D_DID int);

 DECLARE @tbl_GRN_Detail TABLE (
    D_ID INT,
    Item_Code int,
    Item_Qty int,
    Item_Weight decimal(10,2),
    D_DID int);

INSERT INTO @tbl_Bale_Prd VALUES 
('BB-10001',1,1,555,null),
('BB-10002',2,1,410,null),
('BB-10003',3,1,545,null);

INSERT INTO @tbl_GRN_Detail VALUES 
(12001,1,1,555,null),
(12002,6,1,451,null),
(12003,8,1,980,null);
-- DDL and sample data population, end


DECLARE @Bale_ID VARCHAR(12) = 'BB-10001';

 ;WITH rs AS
 (
    Select 'BB-' + TRY_CAST(D_ID AS VARCHAR(12))  as Bale_ID
        ,item_code,Item_Qty as Bale_Qty
        ,Item_Weight as Bale_WEight
        ,D_DID 
    FROM @tbl_GRN_Detail  
)   
SELECT Bale_ID
    , item_code
    , Bale_Qty,Bale_Weight
    , D_DID 
 FROM @tbl_Bale_Prd  
 WHERE  Bale_ID = @Bale_ID and (D_DID IS NULL or D_DID=1)
 UNION ALL
 SELECT Bale_ID
    , item_code
    , Bale_Qty
    , Bale_WEight
    , D_DID 
 FROM rs
 WHERE Bale_ID = @Bale_ID and (D_DID IS NULL or D_DID=1);

Analyst_SQL 3,576

Please check below ,row is not retrieving

  DECLARE @tbl_Bale_Prd TABLE (
     Bale_ID varchar(12),
     Item_Code int,
     Bale_Qty int,
     Bale_Weight decimal(10,2),
     D_DID int);

  DECLARE @tbl_GRN_Detail TABLE (
     D_ID INT,
     Item_Code int,
     Item_Qty int,
     Item_Weight decimal(10,2),
     D_DID int);

 INSERT INTO @tbl_Bale_Prd VALUES 
 ('BB-10001',1,1,555,null),
 ('BB-10002',2,1,410,null),
 ('BB-10003',3,1,545,null);

 INSERT INTO @tbl_GRN_Detail VALUES 
 (12001,1,1,555,null),
 (12002,6,1,451,null),
 (12003,8,1,980,null);
 -- DDL and sample data population, end


 DECLARE @Bale_ID VARCHAR(12) = 12003;

  ;WITH rs AS
  (
     Select 'BB-' + TRY_CAST(D_ID AS VARCHAR(12))  as Bale_ID
         ,item_code,Item_Qty as Bale_Qty
         ,Item_Weight as Bale_WEight
         ,D_DID 
     FROM @tbl_GRN_Detail  
 )   
 SELECT Bale_ID
     , item_code
     , Bale_Qty,Bale_Weight
     , D_DID 
  FROM @tbl_Bale_Prd  
  WHERE  Bale_ID = @Bale_ID and (D_DID IS NULL or D_DID=1)
  UNION ALL
  SELECT Bale_ID
     , item_code
     , Bale_Qty
     , Bale_WEight
     , D_DID 
  FROM rs
  WHERE Bale_ID = @Bale_ID and (D_DID IS NULL or D_DID=1);

Yitzhak Khabinsky 26,586 Reputation points

2021-11-25T18:33:34.567+00:00
Your parameter should be as follows:

DECLARE @Bale_ID VARCHAR(12) = 'BB-12003';

And everything is working.
Analyst_SQL 3,576 Reputation points

2021-11-25T18:39:20.293+00:00

But i will not mentioned every time BB, because i have just int value in Database, Could it possible BB add automatically
Yitzhak Khabinsky 26,586 Reputation points

2021-11-25T18:51:47.187+00:00
You can use the following approach:

DECLARE @realParameter VARCHAR(9) = '12003'; DECLARE @Bale_ID VARCHAR(12) = 'BB-' + @realParameter;
Analyst_SQL 3,576 Reputation points

2021-11-25T19:04:29.567+00:00
then what about this ,where i have varchar value in tbl_Prd_bale

DECLARE @realParameter VARCHAR(9) = 'BB-10001'; DECLARE @Bale_ID VARCHAR(12) = 'BB-' + @realParameter;

Answer 3

EchoLiu-MSFT 14,621

Please also check:

 ;with cte  
 as(select Bale_ID,item_code,Bale_Qty,Bale_Weight,D_DID   
 From #tbl_Bale_Prd    
 where  Bale_ID = 'BB-10001' and (D_DID IS NULL or D_DID=1)     
 union      
 Select CONVERT(VARCHAR(15), D_ID) as Bale_ID,  
 item_code,Item_Qty as Bale_Qty,Item_Weight as Bale_WEight,D_DID   
 from #tbl_GRN_Detail)  
   
 select * from cte  
 where  Bale_ID= 'BB-10001' and (D_DID IS NULL or D_DID=1)

Output:

Regards,
Echo

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Analyst_SQL 3,576

I have Bale_ID value is in varchar and int ,when i passing value of int then it is giving error

check with below data

 Create table #tbl_Bale_Prd (Bale_ID varchar(12),Item_Code int,Bale_Qty int ,Bale_Weight decimal(10,2),D_DID int)
 Create table #tbl_GRN_Detail (D_ID int,Item_Code int,Item_Qty int,Item_Weight decimal(10,2),D_DID int)


 Insert into #tbl_Bale_Prd values ('BB-10001',1,1,555,null)
 Insert into #tbl_Bale_Prd values ('BB-10002',2,1,410,null)
 Insert into #tbl_Bale_Prd values ('BB-10003',3,1,545,null)


 Insert into #tbl_GRN_Detail values (12001,1,1,555,null)
 Insert into #tbl_GRN_Detail values (12002,6,1,451,null)
 Insert into #tbl_GRN_Detail values (12003,8,1,980,null)


  ;with cte
  as(select Bale_ID,item_code,Bale_Qty,Bale_Weight,D_DID 
  From #tbl_Bale_Prd  
  where  Bale_ID = 12001 and (D_DID IS NULL or D_DID=1)   
  union    
  Select CONVERT(VARCHAR(15), D_ID) as Bale_ID,
  item_code,Item_Qty as Bale_Qty,Item_Weight as Bale_WEight,D_DID 
  from #tbl_GRN_Detail)

  select * from cte
  where  Bale_ID= 12001 and (D_DID IS NULL or D_DID=1)

Share via

Conversion failed when converting the varchar value 'BB-10001' to data type int.

2 additional answers

Your answer