Sql – Conversion failed: when converting the nvarchar value to data type int

sqlsql server

I create a stored procedure

CREATE PROCEDURE [dbo].[wsp_Display_Invoice]
     @DOID NVARCHAR(50),
     @brcode INT
AS
BEGIN
    SELECT DISTINCT 
        A.Item_Code,
        (B.Description + '-' + B.Product_Code + '(' + D.Brand_Name+ ')') AS Description,
        A.Quantity, B.UOM, A.Rate, A.DOID,
        C.Doc_No, B.Tax_Percentage AS Tax, B.Tax_Percentage AS Tax1,
        F.QuotationID
    FROM 
        DO_T A 
    INNER JOIN 
        inv_Item_Master B ON A.Item_Code = B.Item_Code 
    INNER JOIN 
        DO C ON A.DOID = C.DOID 
    INNER JOIN 
        Inv_Brand D ON D.Brand_ID = B.Brand_ID 
    INNER JOIN 
        Quotation_T F ON F.DOID = A.DOID 
    WHERE 
        A.DOID LIKE '%' + @DOID + '%' 
        AND a.BR_Code = @brcode
END

Got a error

Conversion failed when converting the nvarchar value '22,23' to data type int.

Best Answer

Your code is doing an implicit conversion somewhere. The obvious places are the string concatenation with +. If any of the arguments is a number, then + is interpreted as numeric addition -- and you get an error.

Less obvious places are other operations, such as JOIN conditions. If one column is a string and the other a number, then SQL Server attempts to convert the string to a number -- and you can get an error.

So, I would try the following.

Replace:

   (B.Description + '-' + B.Product_Code + '(' + D.Brand_Name+ ')') AS Description,

with:

   (B.Description + '-' + CAST(B.Product_Code as VARCHAR(255)) + '(' + D.Brand_Name+ ')') AS Description,

This is only necessary if Product_Code is a number.

A.DOID LIKE '%' + @DOID + '%'

with:

CAST(A.DOID as VARCHAR(255)) LIKE '%' + @DOID + '%'

This is only necessary of A.DOID is a number.

Change the type of @brcode to a string. This is only necessary if a_BR_CODE is a number.

If these do not work, you will have to look at the JOIN conditions to see if any of them mix types.

Related Topic