When to write a long stored procedure

Question

Are there any guidelines on when you should and should not write a long complicated 2000+ line stored procedure?

In my specific case this stored procedure contains a lot of if/then, case, goto and branching statements. It works by constructing SQL queries depending on inputs and results of queries and uses the execute statement to run the constructed queries. It can execute several constructed queries in one call and uses results of those queries to construct other queries to run.

It is pretty messy and hard to understand. Debugging is tough, the only way to know what is going on is to step through a call to see what it's doing. There barely any exception handling or logging. Maintaining it is a pain. In fact, no one really knows what it does or how it was created and if we had to make modifications to it we would have to take a "cross your fingers and hope for the best" approach. But, I think it was done this way for performance reasons.

This procedure is used by many applications. The only other way I can think to do something like this is through a web service. It would probably be comparable in complexity, but a lot easier to understand. However, it would probably be multiple times slower as it would still have to make several calls to the database for 1 request.

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

Is there something I'm missing or do we just have to put up with our monstrous stored procedure?

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

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

Answer 1

Sounds like you need to change the way you write and maintain this stored procedure.

A stored procedure is created from a SQL query. Maybe you can write an application/script to generate the SQL to create/update your stored procedure.

Doing this would give you all the benefits of a re-usable stored procedure (the benefits you already have), but it would make maintenance of the stored procedure easier.

By having a 2000+ line stored procedure, you are basically locking yourself in as the only person who can do the job. This means you are stuck where you are and cannot move forward (no job promotion, no assignment to a new project, etc).