Are there stored procedure writing guidelines?

Question

There are a lot of information about how to write an stored procedure from a practical view, in a very database server language dependent way, but since I and the team that I work everyday already know that, I was looking for a more theoretical discussion about how to do this, in some way language or database server independent.

I want to find some analysis like Steve McConnel does in Code Complete for routines, in example.

Where can I find any articles, papers, books or other kind of sources containing theorethical guidelines about how to write an stored procedure?

EDIT No, this is not a duplicate of the question What are the best practices in writing a sql stored procedure since I am asking about a general approach, and not only to Sql Server.

Answer 1

We tend to view stored procs as methods with a signature because the stored proc forms a contract between the database and the client.

• name
• parameters (and where optional because we can't overload)
• number of resultsets
• rows expected (0-1, 1, 0-n, 1-n etc)

We also try to use helper and internal stored procs

Of course, there are some limitations

• signature can't be resolved or enforced in a the client IDE
• any OO analogy only goes so far
• SQL is set based and does things differently

Finally, perhaps evolve a stored proc template. We have one where we:

• always use TRY/CATCH
• log every SQL exceptions
• log the user "last seen"
• use extended properties to help document the code outside of the module

It works for us. Whether right or wrong, at least we're consistent...