When to write a long stored procedure

Question

Are there any recommendations on when you should and should not write a long complex 2000-line stored procedure?

In my particular case, this stored procedure contains many if, then, goto, and branch commands. It works by constructing SQL queries depending on the input data and query results and uses the execute statement to execute the constructed queries. It can execute several built queries in one call and uses the results of these queries to build other queries to run.

This is pretty dirty and hard to understand. Debugging is tough, the only way to know what is going on is to go through the call to see what it is doing. Almost all exception handling or registration. Maintaining this is pain. In fact, no one knows what he is doing and how it was created, and if we had to make changes to it, we would have to take a “cross with our fingers and hope for a better” approach. But I think this was done for performance reasons.

This procedure is used by many applications. The only way I can do something like this is with a web service. It would probably be comparable in complexity, but much easier to understand. However, it will probably be several times slower, since it will still have to make several calls to the database for 1 query.

So, my question (s): how do we decide when and when not to write long stored procedures?

Is there something I am missing, or do we just need to put up with our monstrous stored procedure?

Are there ways to structure and break down stored procedures into smaller components so that they are easy to understand?

Will the stored procedure be always faster than anything else, and the right choice when you need to make many calls in the database?

Answer 1

I am not sure that it is ever a good idea to write 2000+ LOC for one function. My initial reaction is to say that your sproc should be broken down into smaller functions (table or scalar, whatever suits you) and stored procedures (for operations without a request). However, this can simply move the LOC around, rather than simplifying the actual work. Unfortunately, without any source code, it would be difficult to give more specific recommendations.

Answer 2

It looks like you need to change the way you write and save this stored procedure.

The stored procedure is created from an SQL query. Perhaps you can write an application / script to create SQL to create / update a stored procedure.

Doing this will give you all the benefits of a reusable stored procedure (benefits that you already have), but it will simplify the maintenance of the stored procedure.

With a 2000-line stored procedure, you basically lock up as the only person who can do the job. This means that you are stuck where you are and cannot move forward (without promotion, without appointing a new project, etc.).

Answer 3

If you still have this problem, you can try using the Common Language Runtime Integration (CLR), which uses some other Microsoft language like C #, a visual base language, among others, to encode your solution. This will help with the debuggin and maintennace of your code.