Topher's SP Tips

I've always been a big fan of SP's for several reasons:

  1. Encapsulation. Especially in an OO environment, this is virtually mandated. It provides a cleaner development cycle, cleaner code, and more flexability and tunability later on.

  2. Security. You can grant execute on SP's to users but revoke all other accesses except to the SP owner. This eliminates a lot of security/integrity problems.

  3. Testing. SP's allow you independent validation of the front end and the back end.

  4. Portability. (Both front end and back end.)

  5. Tunability.

  6. Monitoring. You can see when an SP is executing and, therefore, know what's happening. You can't know what querries are going on with straight SQL. (BTW, for that same reason, name any temp tables you use in a consistant and unique way; then, you can take a quick look in tempdb and have a picture of what's going on there. If you name all of them #a, #b ....)

Some advice to keep in mind:

  1. Version control them, each with it's own revision header and revision number. Use version control software and a make utility. Keep track of dependencies.

  2. Make the first line of the procedure a comment with the version number and/or date. If you're consistent, you can easily check any system and see what the revisions of the procedures are. You can write a pretty simple SP to look-up the revision levels of the SP's.

  3. Watch your transaction boundries, especially in nested procedures.

  4. Add a debug flag to the parameter list (usually the last parameter). If you use a tinyint, you have 255 levels of debug. Default the parameter to 0 so that if it isn't passed you do no debug reporting. I suggest using level 1 to be basic instrumentation (log the name of the procedure, datetime began/ended, userid, transaction level, etc.) This is nice because you can "turn on" debugging selectively, for only some clients, by hand, whatever, and not have to recompile!

  5. Doccument excessively!

  6. Recompile them regularly.

  7. Tune, tune and re-tune.

  8. You can have a stored procedure use a temp table created by another stored procedure which is further up on the callin hierarchy. Just make sure that the table exists in the session when the procedure is created. This can cause some funky results, especially if the two temp tables (the one at creation and the one at invocation) are different. Be careful! Let me stress, though, that for complex processing this can be a very useful method of having multiple procedures operate on sets of data (essentially, you can use a temp table as a way of passing a table as a parameter).

Disadvantages:

  1. Dynamic sql is a problem.

  2. The debug flag (described above) can cause additional processing.

  3. Procedure cache requires more memory for the server.

  4. Oddball parameters can cause a procedure with a poor querry plan to get cached and used by other users.

  5. Version control is not always straight forward.

    One more note: JAM from JYACC has the cleanest interface for calling stored procedures that I've ever seen. No binding nescessary. 

Topher
---
Christopher "Topher" White   |  "The problem with justice in this country 
McCaw Cellular Communications|    is that there are too many hung juries
Kirkland, Washington         |     and not enough hung defendants."
Standard disclaimers apply   |          -- Dennis Miller