sqlpadawan_1 (4/8/2013)
Imagine a SELECT in a proc that returns active widgets, the WHERE is always going to be the same. So do you code your where clause 'WHERE Active = 'YES'' or WHERE Active = @yes? If I can consistently prove that the variable code uses more CPU and is slower, than in my mind, it's an open and shut case. What does the variable declaration buy you? I'm either lazy or efficient (probably lazy), but in my mind this just clouds the code.
If you are lazy (good on you! 🙂 ) create a view ActiveWidgets with hardcoded literal - and use it in all relevant procedures.
Be careful not to put any calculations/conversions into that view, just row filter (and possibly limited columns).
Otherwise you're gonna create horrible performance issues.
_____________
Code for TallyGenerator