SQL - select * from X WHERE (if value is numeric, else ...)

Question

I would need to make a DB select that will behave differently when number or text is entered, just different condition should be used. I think it should be done but not quite sure with the syntax (MSSQL). Thank you

I would need:

SELECT * 
  FROM X 
 WHERE (IF value passed is numeric = "ID" ELSE "NAME") = Value //ID or Name are columns

Answer 1

This is one way, but your question is a wee bit vague

WHERE
    CASE ISNUMERIC(Something) WHEN 1 THEN x ELSE Y END

Answer 2

two ways:

using or

select ..
from tablename
where (something = clause1 and isnumeric(value) = 1)
or (something = clause2 and isnumeric(value) <> 1)

or maybe using union

select ..
from tablename
where something = clause1 and isnumeric(value) = 1
union all
select ..
from tablename
where something = clause1 and isnumeric(value) <> 1

Answer 3

Simple - just retrieve the numeric values:

SELECT [columns] FROM X WHERE ISNUMERIC(value) = 1

More complex:

SELECT [columns] FROM X WHERE CASE ISNUMERIC(value) WHEN 1 THEN [something] ELSE [something_else] END

Answer 4

Based on your example:

SELECT * 
  FROM TABLE X 
 WHERE (CASE 
          WHEN ISNUMBERIC(@parameter_value) = 1 THEN x.id 
          ELSE x.name
        END) = @parameter_value

...would work, I'd like to stress that the approach is not sargable--it won't perform as well as it should.

If dealing with a single parameter, using an IF ELSE would perform better:

IF ISNUMERIC(@parameter_value) = 1
BEGIN

 SELECT x.*
   FROM TABLE x
  WHERE x.id = @parameter_value

END
ELSE
BEGIN

 SELECT x.*
   FROM TABLE x
  WHERE x.name = @parameter_value

END

The other alternative (which should definitely be considered if dealing with more than one filter criteria) is to use dynamic SQL. I recommend reading The curse and blessings of dynamic SQL before looking at this SQL Server 2005+ example:

DECLARE @paramater_value NVARCHAR(255)

DECLARE @SQL NVARCHAR(4000)
    SET @SQL = 'SELECT x.*
                  FROM TABLE x '

    SET @SQL = @SQL + CASE 
                        WHEN ISNUMBERIC(@paramater_value) = 1 THEN 
                          ' WHERE x.id = @paramater_value '
                        ELSE 
                          ' WHERE x.name = @paramater_value '
                      END   

BEGIN

  EXEC sp_executesql @SQL,
                     N'@paramater_value NVARCHAR(255)',
                     @paramater_value

END

Answer 5

I'm assuming there is code somewhere that constructs this query and sends it to the database? Why not have that code determine whether the value is numeric and generate the appropriate query? It keeps the query cleaner and has the advantage of being compatible with virtually every RDBMS on the planet.