Today I was asked how to return an error code from a database along with other data. While I try to sheer away from error codes from the database and do my validation / logic in the business / data layers this still is necessary. Most people I have worked with have no idea that sql2000 (and 2005) support output parameters which can be quite usefull.

CREATE PROCEDURE GetCustomers ( @ErrorCode int OUTPUT )
AS

SELECT	* 
FROM 	customers

--if there is an error set this to something else
set @ErrorCode = 0
go

In code you would have something like this:

cmd.parameters.add("@ErrorCode", SqlDbType.int)
cmd.parameters("@ErrorCode").Direction = ParameterDirection.Output
reader = cmd.executeReader()
if cmd.parameters("@ErrorCode").value <> 0 then
    LogError()
else
    ... enumerate reader here
end if


So this is a handy way of returning some additional bit of data without having to return another result set. This is something that is fairly rarely used but can be quite handy.

One place I have used this quite effectively is with a dataset centric app that uses the sql application block. I set the identity field in the database to be an output and map that to my dataset. Whenever I insert a record the identity column in the dataset gets updated with the identity from the insert operation. It is beyond the scope of this post to describe it in detail though (and I am hungry) but has been working quite well for me.