Want 'Exists' in a Where clause to be used only when passed parameter has a value

Question

I have a Select like the one below in a stored procedure (shortened for brevity). @param is a parameter to the stored procedure which can be NULL.

SELECT name FROM Table1 WHERE EXISTS (select .... from table2 Where param = @param AND ... AND ...) AND ... AND ...

I would like the EXISTS statement (the part in bold) to be used only when @param has a value otherwise ignore it.

I don't want to use dynamic SQL or temporary tables if possible. I am trying to use a CASE statement to work with the EXISTS statement but with not much luck.

Answer 1

WHERE (@Param IS NULL OR EXISTS (SELECT .... ))

Note that this isn't a guarantee - the query optimizer will do what it wants. But is should be smart enough to optimize the exists clause away.

Answer 2

Using the OR in the WHERE clause will most likely by horrendously slow, especially if EXISTS.

Other options...

Union: only one will return rows

SELECT name FROM Table1 WHERE EXISTS (select .... from table2 Where param = @param AND ... AND ...) AND ... AND ...
UNION ALL
SELECT name FROM Table1 WHERE @param IS NULL AND ... AND ...

Conditional branch:

IF @param2 IS NULL
    SELECT name FROM Table1 WHERE ... AND ...
ELSE
    SELECT name FROM Table1 WHERE EXISTS (select .... from table2 Where param = @param AND ... AND ...) AND ... AND ...