Return to Question

I come from OOP background, and I need to write and maintain huge stored procedures.

Naturally, the sprocs become quite unreadable very quickly, no matter how much I try formatting, giving readable names and aliases, and so on.

Andrew Novic brings up the option of breaking the procedure into subroutines (rule #1 they teach you in school), but points out that this leads to poor performance.

In my case, performance overrules all other considerations, so it looks like I'm stuck with run-on stored procedures. (ORM is out of the question.)

So... is there any way to make my stored procedures more readable,
or is "damage minimilization" (i.e. nice formatting) my only option?

UPDATE - Clarification:

I deliberately left out details, because I reasoned answers would be useful for others as well, and not just my specific case.
However, I'm adding the details to give an idea of what I'm facing.

Here's what makes the SPs unreadable:

• Length (hundreds of lines of code each)
• Dynamic SQL
• Control flow
• Nested queries (sometimes two or even three levels)
• Combinations of all of the above :(

I come from OOP background, and I need to write and maintain huge stored procedures.

Naturally, the sprocs become quite unreadable very quickly, no matter how much I try formatting, giving readable names and aliases, and so on.

Andrew Novic brings up the option of breaking the procedure into subroutines (rule #1 they teach you in school), but points out that this leads to poor performance.

In my case, performance overrules all other considerations, so it looks like I'm stuck with run-on stored procedures. (ORM is out of the question.)

So... is there any way to make my stored procedures more readable,
or is "damage minimilization" (i.e. nice formatting) my only option?

UPDATE - Clarification:

I deliberately left out details, because I reasoned answers would be useful for others as well, and not just my specific case.
However, I'm adding the details to give an idea of what I'm facing.

Here's what makes the SPs unreadable:

• Length (hundreds of lines of code each)
• Dynamic SQL
• Control flow
• Nested queries (sometimes two or even three levels)
• Combinations of all of the above :(

UPDATE 2:

I'm using Sql Server 2k5/2k8

I come from OOP background, and I need to write and maintain huge stored procedures.

Naturally, the sprocs become quite unreadable very quickly, no matter how much I try formatting, giving readable names and aliases, and so on.

Andrew Novic brings up the option of breaking the procedure into subroutines (rule #1 they teach you in school), but points out that this leads to poor performance.

In my case, performance overrules all other considerations, so it looks like I'm stuck with run-on stored procedures. (ORM is out of the question.)

So... is there any way to make my stored procedures more readable,
or is "damage minimilzation" (i.e. nice formatting) my only option?

UPDATE - Clarification:

I deliberately left out details, because I reasoned answers would be useful for others as well, and not just my specific case.
However, I'm adding the details to give an idea of what I'm facing.

Here's what makes the SPs unreadable:

• Length (hundreds of lines of code each)
• Dynamic SQL
• Control flow
• Nested queries (sometimes two or even three levels)
• Combinations of all of the above :(

I come from OOP background, and I need to write and maintain huge stored procedures.

Naturally, the sprocs become quite unreadable very quickly, no matter how much I try formatting, giving readable names and aliases, and so on.

Andrew Novic brings up the option of breaking the procedure into subroutines (rule #1 they teach you in school), but points out that this leads to poor performance.

In my case, performance overrules all other considerations, so it looks like I'm stuck with run-on stored procedures. (ORM is out of the question.)

So... is there any way to make my stored procedures more readable,
or is "damage minimilization" (i.e. nice formatting) my only option?

UPDATE - Clarification:

I deliberately left out details, because I reasoned answers would be useful for others as well, and not just my specific case.
However, I'm adding the details to give an idea of what I'm facing.

Here's what makes the SPs unreadable:

• Length (hundreds of lines of code each)
• Dynamic SQL
• Control flow
• Nested queries (sometimes two or even three levels)
• Combinations of all of the above :(

I come from OOP background, and I need to write and maintain huge stored procedures.

Naturally, the sprocs become quite unreadable very quickly, no matter how much I try formatting, giving readable names and aliases, and so on.

Andrew Novic brings up the option of breaking the procedure into subroutines (rule #1 they teach you in school), but points out that this leads to poor performance.

In my case, performance overrules all other considerations, so it looks like I'm stuck with run-on stored procedures. (ORM is out of the question.)

So... is there any way to make my stored procedures more readable,
or is "damage minimilzation" (i.e. nice formatting) my only option?

I come from OOP background, and I need to write and maintain huge stored procedures.

Naturally, the sprocs become quite unreadable very quickly, no matter how much I try formatting, giving readable names and aliases, and so on.

Andrew Novic brings up the option of breaking the procedure into subroutines (rule #1 they teach you in school), but points out that this leads to poor performance.

In my case, performance overrules all other considerations, so it looks like I'm stuck with run-on stored procedures. (ORM is out of the question.)

So... is there any way to make my stored procedures more readable,
or is "damage minimilzation" (i.e. nice formatting) my only option?

UPDATE - Clarification:

I deliberately left out details, because I reasoned answers would be useful for others as well, and not just my specific case.
However, I'm adding the details to give an idea of what I'm facing.

Here's what makes the SPs unreadable:

• Length (hundreds of lines of code each)
• Dynamic SQL
• Control flow
• Nested queries (sometimes two or even three levels)
• Combinations of all of the above :(