Sql-server – Expression for a result in case

casesql server

I want to give an expression for a result in a case expression in SQL Server. When I checked the

Links : CASE , EXPRESSIONS. But I'm still confused.


Simple CASE expression: 
CASE input_expression 
     WHEN when_expression THEN result_expression [ ...n ] 
     [ ELSE else_result_expression ] 
Searched CASE expression:
     WHEN Boolean_expression THEN result_expression [ ...n ] 
     [ ELSE else_result_expression ] 

I want give result_expression as the following example query.

select * from mytable where
CASE WHEN col1='12' THEN col2 like '%12%' ELSE col3 like '%12%' END

but it giving the error

Incorrect syntax near the keyword 'like'.

Is there any possibilities for this?

Best Answer

SQL Server does not have BOOLEAN type - at least not exposed, internally it has boolean values that are used in conditions - and a CASE expression cannot have a result of such type. See the link above where it explains:

Unlike other SQL Server data types, a Boolean data type cannot be specified as the data type of a table column or variable, and cannot be returned in a result set.

You can rewrite the WHERE condition using AND and OR:

FROM mytable 
WHERE ( (col1 = '12')                  AND (col2 LIKE '%12%') ) 
   OR ( (col1 <> '12' OR col1 IS NULL) AND (col3 LIKE '%12%') ) ;