Blog Post

The Importance of Database Coding Standards


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:







You rated this post out of 5. Change rating




You rated this post out of 5. Change rating