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 |
