Stored Procedure structuring

Often I have seen stored procs used for writing business logic in an application. Sometimes these procs开发者_如何学编程 will contain 1000+ lines of code. If I write a method/function in application code that contained 1000 lines it would be rightly criticised. Should long stored procs be broken down into separate procs, like methods in a class would be? What isn't this done more as it would certainly make code more usable.

It sounds to me like you're to the point where you need to start thinking about a service layer for your database. This will allow you to move the business logic into a more appropriate language for lots of procedural code, while still enforcing access to the database through your approved api.

First, I agree with Nat's answer: the tools (such as debuggers) for T-SQL debugging provide nowhere near the functionality that one finds for other languages.

Second, there are a number of potential challenges when passing values between stored procedures. Passing simple data types is straight-forward. Passing involved data types becomes more complex. Using temporary tables, XML, delimited strings, record sets, etc. require additional coding, create additional overhead, and have performance implications.

My "rule" is that if the input and output parameters can be handled with the standard methods (i.e. standard data types), then breaking up the stored procedure is warranted. If passing the input and output requires a lot of coding effort, then the stored procedure remains large.

I think "lines of code" is a poor measure of how reusable the code is. I think you need to take a much more qualitative look at these "long" procedures. I've had several long procedures in the past, and whether any of the code can be shortened and modularized really depends - is any of the logic really reused by other applications or is this more of a textbook desire? I am sure there are plenty of modules out there in enterprise applications that are more than 1000 lines of code and don't need to be criticized or broken down into smaller parts...

Does that mean the procedures you've seen that are 1000+ lines of code are justified? Of course not. I just wanted to stress that number of lines of code is not the only factor you should be looking at.

Absolutely, long stored procs should be broken down into short blocks of code that are re-usable and robust. Unfortunately, the language and tools used in database development don't support doing this in a practical manner.

When your SP is so big, you can definitelly say that this procedure 'does many things'. If so, you should do any of these things separatelly. As I can see in my expirience, if you need to support this functionality, easy to refactor such SP one time than work with 1000 lines of spaghetti-code.