Are writing long stored procs a sign of bad database design and coding style?

Question

Hello,

I've met one person who claims he has wrote to over 5000 lines of stored procedure in Sql Server. Is that long stored procedure actually required ever? Or is it sign of a bad database and coding design? I am really worried. I never wrote anything long than 100 lines of code in Stored Procedure in one my live projects based on Insurance which had to deal with U.S Acords and Supplements. Btw, he was boasting as if he did some great job writing such long stored procedure.

Thanks in advance :)

Waiting for your replies.

Answer 1

From my personal experience, there are several conditions that force you to write such a long stored procedure, these are 2 of the ones that I'm remembering right now

• Bad database design (as you already mentioned)
• A big complicated formula for calculating sth very very complicated, reports etc. (e.g. Human Resources - Leave Management)

I don't think that writing such a long stored procedure is a good thing, I personally think that it's a pretty bad thing, it shows that the database is messed up, not planned thoroughly enough.

Answer 2

I don't think the length of a stored procedure is a sign of bad design or coding style.

I think the code in a stored procedure is a sign of bad design or coding style.

He could have wrote

SELECT
*
FROM
TABLE
AS
t

That could grow pretty quickly, as you know that could fit on one line, maybe he thinks its more readable.

The point being, I don't think the length is a good metric to determine whether a sproc is good or bad. You can probably write a 100 line sproc which is worse than a 5000 line sproc.

If I personally was looking at my brand new 5000 line sproc, I would be worried and not proud, even if there was no other choice. Maintaining that will be a nightmare.

But in your friends case it's difficult to comment either way without knowing either what action the sproc performs or how it does it.