SDTimes has an article up about Debunking Cyclomatic Complexity that talks about the results of some research that supports what I suspect most of agree with - more lines of code per module/method leads to more bugs. Object oriented program has done a great job in leading away from procedures that have 500 lines of code down to 50 or less (we're nowhere close to winning that battle in TSQL!). But the interesting part was that more complex code didn't correlate to more bugs, and speculates on some ideas about why not - I'll leave that for you to read.

Seriously, on the TSQL side you see what are relatively trivial procs on one side (which are fine and useful), and on the opposite extreme you see almost entire programs written in the context of a single proc. There is real value in breaking up those monoliths that are easier to read, test, and possibly even reuse.