CODEWITHSUNDEEP
sqlsql-server
Omer
Omer

Reputation: 133

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

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

Upvotes: 1

Views: 6584

Answers (1)

Jibin Balachandran
Jibin Balachandran

Reputation: 3441

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

Upvotes: 1

Related Questions