All geek questions in one place

Is it ... Good / Bad / Good ... use IF / While Conditions in stored procedures?

My main problem is with SQL Server 2005 ... I went through many websites, and each one tells something different.

What are the scenarios that are good / normal to use. For example, it hurts to even set the values ​​of variables inside IF, or only if I run a query. Assuming my SPs are building dynamic SQL based on several conditions in Input Parameters, I need to reconsider the query question ... As for the SP, which starts another query based on whether there is any record in the table. etc. etc. My question is not limited to these scenarios ... I am looking for a slightly more generalized answer to improve my future joint ventures

In essence ... What statements are good to use in the conditions of branching / loop, which is bad, and this is good.

+4
source share
3 answers

Typically ... Avoid procedural code in your database and stick to queries. This gives the query optimizer the ability to do its job a lot better.

The exceptions will be code that is designed to perform many actions, and not to create a set of results, and when a query requires strings to be linked exponentially to get the result.

+5
source

It is very difficult to answer this question if you do not provide any code. No language construct is Good / Bad / Good in itself, it is what you want to achieve, and how well it can be expressed with these constructs.

+1
source

There is no final answer, as it really depends on the situation.

In general, I find it best to keep the logic in sproc as simple and set-based as possible. For example, this is too complicated with several IF statements nested, it can complicate it for the query optimizer, which means that it cannot create a good execution plan suitable for all paths via sproc. For example, the first time sproc is started, it takes path A through logic, and the execution plan reflects this. The next time it starts with different parameters, it takes path B, but re-executes the original execution plan, which is not optimal for this second path. One solution to this is to break down the load into separate stored procedures for calling depending on the path used - this allows you to optimize the routine and execution plan, cached independently.

Loops may be the only viable option, but overall I would try not to use them - always try to do something based on a set, if possible.

0
source

Source: https://habr.com/ru/post/1299300/

More articles:


All Articles