One thing I see developers do quite a bit is try to “fix” a parameter in an IF branch.
Maybe it’s to protect against bad search values, but more often it’s to nix NULLs.
I know that the stored procedure I’m showing only has one branch in it where a query is executed, and the series is supposed to be about if branching with multiple queries.
I’m only doing that to simplify the point of this post, which is that “fixing” supplied values does not correct performance and cardinality estimation issues with queries in IF branches.
Click through for the example and a brief round-up of several things which don’t work.