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

Question

I am facing TWO MAJOR PROBLEMS!!

PROBLEM 1:

I have two tables and want to show the required data into a specific gridview by using UNION in SQL. Table 1 contains columns {[Date] datetime, [Head] nvarchar(50), [Details] nvarchar(360), [ExpId] int, [Amount] decimal(18,2)} Table 2 contains columns {[Purchase_Date] datetime, [VendorName] nvarchar(50), [Remarks] nvarchar(50), [Bill_No] nvarchar(50), [AmountPaid] decimal(18,2) }

My stored procedure is;

DECLARE @Ledger TABLE
    (       
    DATE DATETIME,
    DESCRIPTION NVARCHAR(350),
    REF_NO NVARCHAR (50),
    AMOUNT INT      
    )

INSERT INTO @Ledger
    SELECT  
        [Date], [Head] + ' - ' + [Details], [ExpId], [Amount] 
    FROM 
        [dbo].[Table1]
    UNION  
    SELECT  
        [Purchase_Date], 'PURCHASE' + ' ' + [VendorName] + ' ' + [Remarks], [Bill_No], [AmountPaid]  
    FROM 
        [dbo].[Table2]

SELECT * FROM @Ledger

When is execute the query I get an error

Conversion failed when converting the nvarchar value 'ABC113973' to data type int.

I wonder why its throwing this error when I try to execute it without Table1 it's fine. Is is due to the column ExpId with datatype int? If yes then how to deal with it?

PROBLEM 2:

In the above @Ledger table when I change Amount datatype to decimal(18,0) as I want to show the result in decimal figure it throws error

Conversion failed when converting varchar into numeric

sort of error. as the datatype of amount columns of both the actual tables are decimal(18,2).

Can anyone tell me the solution and the reasons of this problem? Thanks

Please see the edited question. the datatype is INT for ExpId. — Omer
this is the cause of error , the columns in union must have same datatype..seebelow answer — Mudassir Hasan

Jibin Balachandran Jibin Balachandran · Accepted Answer · 2016-12-08T05:26:06

Try this:

DECLARE @Ledger TABLE
    (       
    DATE DATETIME,
    DESCRIPTION NVARCHAR(350),
    REF_NO NVARCHAR (50),
    AMOUNT INT      
    )
INSERT INTO @Ledger
    SELECT  [Date], [Head] + ' - ' + [Details], CAST([ExpId] AS NVARCHAR(50)), [Amount]  FROM [dbo].[Table1]
    UNION  
    SELECT  [Purchase_Date], 'PURCHASE' + ' ' + [VendorName] + ' ' + [Remarks], CAST([Bill_No] AS NVARCHAR(50)), [AmountPaid]  FROM [dbo].[Table2]
SELECT * FROM @Ledger

You are getting an error because you are trying to insert bill_no and expid into a varchar column,but the data type of both fields are int. So, you will have to either cast or convert the int values and then insert it into the table.

Edit:

If you want to store the amount in Ledger table as decimal then change the data type of amount column to decimal(18,2) and make sure that columns of both the actual tables are also of the same data type.

DECLARE @Ledger TABLE
    (       
    DATE DATETIME,
    DESCRIPTION NVARCHAR(350),
    REF_NO NVARCHAR (50),
    AMOUNT DECIMAL(18,2)
    )

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

1 Answers