execution plan with and clause in where
OPTION (RECOMPILE) is your friend here. SQL Server has no clue what the value for @i will be at execution time, so it compiles a plan that will work for any parameter value.

Forcing recompile will override that behaviour and produce a plan with a constant scan.

Example:

DECLARE @i int = 1;
SELECT *
FROM master.dbo.spt_values
WHERE @i = 0
OPTION (RECOMPILE);


If you capture the actual execution plan, it will contain only a constant scan. Without OPTION (RECOMPILE) it contains the regular plan.