none
Error Message conversion failed when converting the nvarchar value to data type int??

    Question

  • Hi,

    I can't seem to fix the following error in my stored procedure.

    Error Message: Conversion failed when converting the nvarchar value '1007-001' to data type int.

    The line of code in my stored procedure that seems to be the problem is the following:

    CASE WHEN [Order Details].[Job No] IS NULL THEN [Orders].[Order No] ELSE [Order Details].[Job No] END

    Order No has a data type of INT and Job No has a data type NVARCHAR(8). In the above case statement i'm not trying to convert anything but just display a column depending on the out come of the case statement. If anyone knows how to get around this error you help would be very welcome.

     

    Friday, January 19, 2007 2:32 PM

All replies

  • hi,

     Aston35 wrote:

    Hi,

    I can't seem to fix the following error in my stored procedure.

    Error Message: Conversion failed when converting the nvarchar value '1007-001' to data type int.

    The line of code in my stored procedure that seems to be the problem is the following:

    CASE WHEN [Order Details].[Job No] IS NULL THEN [Orders].[Order No] ELSE [Order Details].[Job No] END

    Order No has a data type of INT and Job No has a data type NVARCHAR(8). In the above case statement i'm not trying to convert anything but just display a column depending on the out come of the case statement. If anyone knows how to get around this error you help would be very welcome.

     

    as you pointed out, the 2 columns are of a different data type, so you have to deal accordingly.. as you can not convert 'abc' to an integer data type, you have to perform the opposite, convert the int to a varchar...

    SET NOCOUNT ON;
    DECLARE @b int;
    SET @b = 0;
    PRINT 'works';
    SELECT CASE WHEN @b = 0 THEN 1 ELSE 'a' END AS [Result];
    
    SET @b = 1;
    PRINT 'does not work';
    SELECT CASE WHEN @b = 0 THEN 1 ELSE 'a' END AS [Result];
    GO
    DECLARE @b int;
    SET @b = 1;
    PRINT 'works';
    SELECT CASE WHEN @b = 0 THEN CONVERT(varchar, 1) ELSE 'a' END AS [Result];
    --<---------
    
    works
    Result
    -----------
    1
    
    does not work
    Result
    -----------
    Msg 245, Level 16, State 1, Line 9
    Conversion failed when converting the varchar value 'a' to data type int.
    
    works
    Result
    ------------------------------
    a
    

    regards
    Friday, January 19, 2007 3:38 PM
    Moderator
  • Thanks Andrea, will give your suggestion ago and see how i get on.
    Friday, January 19, 2007 4:27 PM