2 added caveat
source | link

I just ran a test in my environment against AdventureWorks and found that the CASE approach works best. Using the OR approach made the engine use the clustered index where the CASE statement let it use the non-clustered index.

so your query could look like this.

SELECT *
FROM MyTable
WHERE CASE WHEN @MyParam = 0 THEN 1
           WHEN MyColumn = @MyParam THEN 1
           ELSE 0
           END = 1

However, if as you say there are lots of parameters then it's unlikely you are going to be able to use an index in any case. So you might leave it alone. My testing was for a simple query.

I just ran a test in my environment against AdventureWorks and found that the CASE approach works best. Using the OR approach made the engine use the clustered index where the CASE statement let it use the non-clustered index.

so your query could look like this.

SELECT *
FROM MyTable
WHERE CASE WHEN @MyParam = 0 THEN 1
           WHEN MyColumn = @MyParam THEN 1
           ELSE 0
           END = 1

I just ran a test in my environment against AdventureWorks and found that the CASE approach works best. Using the OR approach made the engine use the clustered index where the CASE statement let it use the non-clustered index.

so your query could look like this.

SELECT *
FROM MyTable
WHERE CASE WHEN @MyParam = 0 THEN 1
           WHEN MyColumn = @MyParam THEN 1
           ELSE 0
           END = 1

However, if as you say there are lots of parameters then it's unlikely you are going to be able to use an index in any case. So you might leave it alone. My testing was for a simple query.

1
source | link

I just ran a test in my environment against AdventureWorks and found that the CASE approach works best. Using the OR approach made the engine use the clustered index where the CASE statement let it use the non-clustered index.

so your query could look like this.

SELECT *
FROM MyTable
WHERE CASE WHEN @MyParam = 0 THEN 1
           WHEN MyColumn = @MyParam THEN 1
           ELSE 0
           END = 1