SQL Design opinion

[#1]

I often code a multi-step "procedural" Stored Proc instead of a complex SQL statement. My reasoning is: when the SQL statement produces erroeous results (especially after it is in production!), the only way I know to debug it is to break the SQL into components and test one part at a time (and risk introducing another bug when reassembling the parts). On the other hand, it is fairly easy to add debug statements to a Stored Proc and/or see intermediate results in the debugger.

I understand the tradeoffs to be: - the complex SQL statement would no doubt be more efficient. - but when under pressure to solve a production problem, the Stored Proc and debugger are quick and easy.

I am just curious if others have an opinion or other tradeoffs to note?

Thanks

[Breck_Carter]

> multi-step "procedural" Stored Proc instead of a complex SQL statement

I am sure that does not mean "record-oriented fetch loop instead of set-oriented SQL statement", but in case it does, please reconsider.

If necessary (to preserve your sanity) try separate set-oriented SQL operations to divide-and-conquer a single complex operation. Sometimes that means using intermediate temporary tables, but not always... views are often a great help; e.g., FROM derived tables, local WITH SELECT view clauses, CREATE VIEW statements.

Sometimes people use fetch loops so they can COMMIT a long UPDATE or DELETE at intermediate points along the way; before doing that, consider an ordinary loop containing a repeated set-oriented UPDATE TOP START AT plus COMMIT. Fetch loops are fraught with inter-connection conflict issues that don't affect atomic UPDATE statements; if you need proof just read the rules for DECLARE CURSOR NO SCROLL - DYNAMIC SCROLL - SCROLL - INSENSITIVE - SENSITIVE (tip: anything other than INSENSITIVE is asking for trouble).

[Former Member]

OUCH. Your comments have revealed some problems with my cursor use: 1) I always declare NO SCROLL since the documentation says "it is the most efficient", INSENSITIVE is all I am looking for so I guess that is the keyword I should use, and 2) I realize I have missed FOR READ ONLY in quite a few.

I do use views. I do use temp tables; in more complex situations I declare them as regular tables and use CURRENT USER as part of the PK so I can later browse the data the user created.

It isn't that we face a huge number of bugs, but since we are outside consultants, it is often sometime before an issue reaches us and when it does I like to be able to nail it down quickly when the user has long forgotten what they were doing at the time.

I’ll get busy and fix all my DO-FOR cursors. Thanks!