Conditionally OR vs CASE vs IF
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