SQL Server select where sql_variant equal doesn't work? [closed]

Question

I'm using SQL Server 2008

I have a column called varEnteredValue of datatype sql_Variant.

When I execute the following select statement I don't get anything

SELECT * FROM tblname WHERE varEnteredValue = 1 

Notice I have many values with value 1 in this column

Question

How can I fix this issue? Do I need to convert data type ?

Answer 1

There are two problems here:

1. You need to convert the value from SQL_VARIANT to INT.
2. You need to be careful about how you convert the values, since not every value in the column will necessarily be convertible to an INT, and it can be difficult to predict whether SQL Server will filter first or try to convert first. You need to test first if the value is numeric, and in order to do that, you must explicitly convert it to a string.

Here is an example that works around both issues:

WHERE CASE WHEN ISNUMERIC(CONVERT(VARCHAR(32), varEnteredValue)) = 1 
  THEN CONVERT(FLOAT, varEnteredValue) ELSE 0 END = 1;

Why are you using SQL_VARIANT?