The Importance of Database Coding Standards

, 2006-08-17

I was working with a developer the other day and the question came up

of how to return status from a stored procedure. There's basically

four ways to do this in SQL Server:

  • return code from the stored procedure
  • use an output parameter
  • return it as a resultset/recordset
  • indicate errors through the use of RAISERROR

The last one would only fire in the event of an issue, but the first

three have the capability to return a particular value upon success.

Given that there are several options, it's important, especially in

larger organizations, to set a standard. Of course, setting a standard

also implies the standard will be enforced. When I hear folks talk

about coding standards, I don't typically see little things like this

get included. But if it is, then developers can anticipate a consistent

mechanism of returning status no matter who coded the stored procedure

(again, assuming proper enforcement of standards). This can reduce

coding time, as a developer won't have to check the definition of each

and every stored procedure to figure out how to get status back.

And this actually raises another issue. Do we always need to return a

status? For the developer in question, some of his proposed stored

procedures returned a status. Others did not. In looking over the

proposal, all of them probably needed to return status. For instance,

one stored procedure was a search procedure. He had not determine to

return status. This meant that so far as an application was concerned,

there was no way to differentiate between a call using the stored

procedure that had an issue and call using the stored procedure where

the search simply didn't return any results. Both cases returned empty

resultsets and left it at that.

Had the developer done anything wrong? Technically, no. His

organization hadn't written standards covering this area. While they

had naming standards for table, views, stored procedures, and other

database ojects, nothing actually covered how best to implement stored

procedures or what kind of T-SQL was acceptable and what needed to be

justified. The use of cursors, because of the performance implications,

come immediately to mind in the latter case.  The use of the old

outer join syntax (*= or =* in the WHERE clause) is another.

Coding standards aren't just for languages such as Visual Basic,

Python, C#, Perl, and others. An organization should define database

coding standards as well.

Technorati Tags:










Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.


1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...


1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.


360 reads