Is it.. Good / Bad / Okay ... to use IF/While Conditions in Stored Procedures ?

Question

My primary concern is with SQL Server 2005... I went through many website and each tells something different.

What are the scenarios that are good / ok to use.. For example does it hurts to even set variable values inside IF or only if I run a query. Supposing my SPs is building a dynamic SQL based of several conditions in Input Parameters, do I need to rethink about the query... What about a SP that runs different query based on whether some record exists in the table. etc.. etc.. My question is not just limited to these scenarios... I'm looking for a little more generalised answer so that I can improve my future SPs

In essense... Which statements are good to use in Branching conditions / Loops, which is bad and which is Okay.

Answer 1

Generally... Avoid procedural code in your database, and stick to queries. That gives the Query Optimizer the chance to do its job much better.

The exceptions would be code that is designed to do many things, rather than making a result-set, and when a query would need to join rows exponentially to get a result.

Answer 2

It is very hard to answer this question if you don't provide any code. No language construct is Good/Bad/Okay by itself, its what you want to achieve and how well that can be expressed with those constructs.

Answer 3

There's no definitive answer as it really depends on the situation.

In general, I think it's best to keep the logic within a sproc as simple and set-based as possible. Making it too complicated with multiple nested IF conditions for example, may complicate it for the query optimiser meaning it can't create a good execution plan suitable for all paths through the sproc. For example, the first time the sproc is run, it takes path A through the logic and the execution plan reflects this. The next time it runs with different parameters, it takes path B through but resuses the original execution plan which is not optimal for this second path. One solution to this is to break the load into separate stored procedures to call depending on the path being followed - this allows that sub sproc to be optimised and execution plan cached independently.

Loops can be the only viable option, but in general I'd try to not use them - always try to do things in a set-based fashion if it is possible.