You're thinking procedurally. SQL Server has to generate an execution plan that can take any value or none for any given parameter. It's not a runtime environment, like .NET or JAVA, it's a "declarative language". So when you say, "The variable can be this or can be that", it has to build a single plan that handles both of those correctly. To the optimizer, "correct" is more important than "efficient", so if you give it a query that can have wildly different "most efficient means to execute", you end up with a plan that has to cover all eventualities, and thus will be less efficient.

There are ways to work around that. Various flavors of parameterized dynamic SQL, nested procs, With Recompile, etc., are all popular and effective methods of essentially generating a different query for different parameter combinations.

I tend to use dynamic SQL for that kind of thing.

DECLARE @SQL NVARCHAR(max) = '

SELECT *

FROM dbo.MyTable

WHERE 1 = 1';

IF @Parameter1 IS NOT NULL

SET @SQL += '

AND MyColumn = @Parameter1';

EXEC sp_executeSQL @SQL, '@Parameter1 INT', @Parameter1;

That kind of thing. Don't actually build the values into the string, pass them in as parameters.

Then, if a parameter value is null or blank, it simply doesn't get added to the Where clause, and you end up with a highly optimizable query, containing only those criteria that actually matter at runtime. No over-broad execution plans because of CASE or OR in Where.