Patterns for stored procedures?

Question

Are there any patterns for writing stored procs? like:

1. Should we write 1 Sp for update, 1 for insert and 1 for select for each table.
2. When is it good to create views.
3. How to manage business rules in SP?
4. How to reduce duplicate code etc etc..

Any good article or book to read about these patterns...

thanks

Answer 1

Typically, stored procs follow a CRUD (http://en.wikipedia.org/wiki/Create,_read,_update_and_delete) pattern, but they don't have to be limited to this.

1) You probably want to combine update and insert. If the primary key is filled then it's an update, otherwise it's an insert. For GUID's and other client-generated keys, you update first, and if the rowcount is zero, you need to do an insert. The insert/update proc typically returns a primary key.

You're going to want a get_by_id proc as well as some number of get_by_non-unique-criteria, which return any number of rows. The idea is to make sure the columns in the result set are identical in all of the get/list procs.

The target of these procedures isn't necessarily one table, or even one view, but rather a logical entity, so you may need to do various joins, both for relationships and lookups. It may help to return multiple result sets.

2) Views are handy, but not particularly relevant to this. Sometimes they help with reuse, but not always.

3) It all depends on the business rules. You can certainly prevent certain kinds of data corruption at the stored proc level, but there are limits to how much it can or should know. Having said that, there are special-purpose cases where you might want to put more logic in the proc, such as login.

4) You can't always do so, but you can sometimes factor out common code into lower-level stord procs that the user doesn't call.

Hope this is a start.