Maybe I'm misunderstanding param sniffing, but isn't the optimizer going to generate a plan and keep it until something changes (index rebuilt\reorg, stats update, table records added\deleted, etc...), and then generate a new plan based on the updated objects? Where the variable value never changes, param sniffing won't come into play here?

If the value never changes in your where clause, you have some really boring code?

😛

Again, it all depends upon performance. That is why I suggested looking at run time and i/o. If you can live with a generic plan that uses variables, why not?