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

Question

Below is data ,i am facing error

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

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

Answer 1

 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.

Answer 2

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);

Answer 3

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".