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

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.

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|||Thanks Andrea, will give your suggestion ago and see how i get on.