Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

K. Brian Kelley - Databases, Infrastructure, and Security

IT Security, MySQL, Perl, SQL Server, and Windows technologies.

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: | | | | |

Comments

Posted by Rob Nickolaus on 17 August 2006
As so much of our IT world centers around data (and thus databases) it amazes me that there aren't commonly accepted standards as in other languages.

Much of what your comments allude to are in regards to notifying calling code what happened, but the standards issue also expands into areas such as formatting, variable naming, commenting, and even source control.

I think that many organizations need a helping hand, possibly boilerplate from a major vendor or a standards group, to create their own coding standards. I personally would love to see the SQL version of Sun's "Code Conventions for the Java Programming Language" (http://java.sun.com/docs/codeconv/).

Maybe I'd write it if there weren't so darn much data to move around :)
Posted by Anonymous on 18 August 2006
Brian Kelley, noted author and database guru, offers insight on the necessity of T-SQL coding...
Posted by K. Brian Kelley on 19 August 2006
Rob, I do agree with you, coding standards do need to go beyond notifying calling code on what happened. All of what you indicated should be defined by an organization in order to ensure consistency. I know Andy posted about not being too restrictive about coding standards and I agree with him, but there's got to be some commonality or it becomes extremely difficult to support and/or extend code another person wrote.

Perhaps Microsoft needs to talk a page out of Sun's book and establish solid T-SQL coding standards. Certainly some of their own internal groups have something to build on.
Leave a Comment

Please register or log in to leave a comment.