Conversion failed when converting the varchar value 'N/A' to data type int.

Question

I am getting error ,when i am using alphabet with int

Create  table #tbl_Prd (ID int,QTY int)
insert into #tbl_Prd values(1,2222)
insert into #tbl_Prd values(1,2222)

select isnull(QTY,'N/A')QTY  from #tbl_Prd

Answer 1

Please keep in mind that there is a data type precedence in T-SQL:
https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-ver16

When an operator combines expressions of different data types, the data type with the lower precedence is first converted to the data type with the higher precedence. If the conversion isn't a supported implicit conversion, an error is returned.

In your case int has the number 16 and char the number 28. So char is converted to int. You will get an error if this is not possible.

You have to cast the int to char before or use two int values.

Create  table #tbl_Prd (ID int,QTY int);
insert into #tbl_Prd values(1,2222), (2,2223), (3,NULL);
select ID, isnull(cast(QTY as varchar(6)),'N/A') QTY  from #tbl_Prd;
select ID, isnull(QTY,-1) QTY  from #tbl_Prd;
go
Drop table #tbl_Prd;

Answer 2

Actually, Christoph's explanation is not accurate. There is no type precedence here. isnull always returns the data type of the first argument. Which is int, and 'N/A' cannot be converted to int. But this works:

DECLARE @str varchar(20)= 'aaa'
SELECT isnull(@str, 0)

The return type is still a string, and data type precedence is not in play.

But to confuse matters, there is also coalesce which is similar to isnull, similar but not equal. coalesce looks like a function but it is not, it is a CASE expression in disguise.

That is

DECLARE @str varchar(20)= 'aaa'
SELECT colaesce(@str, 0)

Is the same as

DECLARE @str varchar(20)= 'aaa'
SELECT CASE WHEN @str IS NOT NULL THEN @str ELSE 0 END
   

And in this case data type precedence sets in; the return type of a CASE expression is the THEN/ELSE type with the highest precedence. And according to what Christoph said, the above will die with a conversion error.

Answer 3

@Christoph Muthmann correctly explained that SQL Server will attempt to convert the "N/A" value to int due to data type precedence rules, which results in an error because "N/A" is not a valid integer.

Converting a NULL value to "N/A" is arguably a job best done in the application data presentation layer rather than T-SQL. That being said, you avoid the conversion error by explicitly casting the int value to varchar(11) so that both values are strings:

DROP TABLE IF EXISTS #tbl_Prd;
CREATE  TABLE #tbl_Prd (ID int, QTY int);
INSERT INTO #tbl_Prd VALUES
	 (1,2)
	,(2,11111)
	,(3,NULL);

SELECT ISNULL(CAST(QTY AS varchar(11)),'N/A') AS QTY
FROM #tbl_Prd;

DROP TABLE IF EXISTS #tbl_Prd;

This method does have drawbacks. Ordering results will be done based on string sorting rather than numeric sorting so larger numbers may sort before lower ones. Adding ORDER BY QTY to the above query returns these results where the larger number sorts before the lower one:

QTY

11111

2

N/A