SQL Server select where sql_variant equal doesn't work?

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 ?

Accepted Answer

There are two problems here:

- You need to convert the value from `SQL_VARIANT` to `INT`.

- 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`?

SQL Server select where sql_variant equal doesn't work?

Solution

Problem