Conditionally OR vs CASE vs IF Lets say, I have a query,

SELECT * FROM MyTable WHERE MyParam = 0 OR MyColumn = MyParam


Here MyParam is parameter and optional. So, it only check MyColumn = MyParam if MyParam is not 0. But our DBA is saying OR will makes it slow and db will suffers. Another option is,

IF MyParam = 0 
SELECT * FROM MyTable WHERE MyColumn = MyParam


The problem with this approach is that we have lot of optional parameters. So, our query become very very big. Another option is CASE.

So what you guys suggest. I am talking about in general whether Oracle or SQL Server.
In sql-server:

One option is dynamic sql, another is option (recompile).

using option (recompile): 

select * from MyTable where MyParam = 0 or MyColumn = MyParam option (recompile);


dynamic sql example:

declare @sql nvarchar(max);

if nullif(@MyParam,0) is not null
  begin;
  set @sql = 'select * from MyTable where MyColumn=@MyParam;'
  exec sp_exeuctesql @sql, '@MyParam int', @MyParam;
  end;
else 
  begin;
  set @sql = 'select * from MyTable;'
  exec sp_exeuctesql @sql;
  end;


Reference:


Parameter Sniffing, Embedding, and the RECOMPILE Options - Paul White
Dynamic Search Conditions - Erland Sommarskog
Catch-all queries - Gail Shaw
An Updated "Kitchen Sink" Example - Aaron Bertand