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: DATABASE SQL | T-SQL | SQL Server | Microsoft SQL Server | SQL Server 2000 | SQL Server 2005