CODEWITHSUNDEEP
sqlsql-servercasting
Zlatan
Zlatan

Reputation: 698

Convert column value to int in SQL Server

I am working on this query and it is returning exception cannot convert varchar to int:

 SELECT BG_PRIORITY, count(*) as cnt 
 FROM Bug 
 WHERE BG_TARGET_REL= case when ISNUMERIC('XXXX 13.5') = 1 then 
 cast('XXXX 13.5' as int) else 'XXXX 13.5' end 
 GROUP BY BG_PRIORITY

This query is generated from my C# code. Where clause filter can be a numeric or string one as the user chooses the type of filter he/she wants and according gives its value.

Is there a approach so that I can add any type of filter in my query?

Upvotes: 2

Views: 1217

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

IF your only column types are int and varchar, then you can simply use this:

SELECT BG_PRIORITY, count(*) as cnt 
FROM Bug 
WHERE <ChosenColumn> = 'InputValue'
GROUP BY BG_PRIORITY;

This works by using SQL Server's implicit data type precedence conversions. If is an int, the input value, something like '123' is converted to the number 123 (exactly what we want!). If the column is varchar, the quoted value remains as a varchar.

Your original CAST error stems from the fact that a CASE statement results in one datatype and one only, using the same data type precedence rules linked above. Consider your branches:

case when ISNUMERIC('XXXX 13.5') = 1
     then cast('XXXX 13.5' as int)      --<< this branch returns int
     else 'XXXX 13.5'                   --<< this branch returns varchar
     end
       >> data type precedence ==>  resultant type of expression is "int"

When you give it a value 'XXXX 13.5', the case statement results in 'XXXX 13.5', which it then needs to cast to the CASE expression resultant type, i.e. int => fail.

Upvotes: 4

Related Questions