CASE in where clause

SQL Apprentice Question
want to create SQL (maybe stored proc) using a SELECT statement that can
add WHERE conditions if (and only if) values are sent to it. For example:

SELECT ID, Person
FROM People
WHERE LastName LIKE '%@var1%' +
CASE WHEN @var2 IS NOT NULL THEN ' AND TypeCode = ' + @var2
ELSE ''
END


....etc


Is this possible? Thanks

Celko Answers
Fundamental mistake! There is no CASE **statement** in SQL. There is
a CASE **expression**; remember programming 101? Expressions return
scalar values, not control of execution flow.

SELECT person_id, person_name
FROM People
WHERE last_name LIKE '%' + @var1 + '%'
AND foobar_code = COALESCE(@my_code, foobar_code) ;


Even for an example, you had some pretty awful data element names.
Something can be a type of something or a code. It cannot be both.
There is no such thing as just an "id" -- it has to identify something
in particular.


You might want to get a book on SQL and data modeling.

The problem is that SQL is set-oriented and not sequential. The THEN
clauses in a CASE expression (which includes COALESCE()) all have to be
evaluated to determine the data type of the whole expression. It does
not matter if some of them are unreachable.

COALESCE correctly promotes its arguments to the highest data type in
the expression:


13 / COALESCE(CAST(NULL AS INTEGER), 2.00) = 6.5


The proprietary ISNULL() uses the first data type and gets things wrong


13 / ISNULL(CAST(NULL AS INTEGER), 2.00) = 6


You would need to write:


13 / ISNULL(CAST(NULL AS DECIMAL(4,2)), 2.00)