More dead kittens

A co-worker of mine enjoys using the axiom, "Everytime you put business logic in the database, god kills a kitten". Needless to say, we have a large quantity of dead kittens due to our application. Kittens or no, my take is that if you are going to put logic in the database, you better be doing error handling.

So imagine my distain when I told not to put a transaction and try...catch in a stored procedure performing multiple inserts and updates but to spin up a SqlTransaction object in C# encompased in a try catch in the application.

You may ask what the difference is. There is still a transaction and catching of errors to avoid a nasty message bubbling up to the client.

The biggest advantage I see on putting the extra code onto the database is that as soon as the error occurs the batch is terminated. Using the C# route to accomplish the rollback and error handling, allows statements to execute after the initial failure. Thereby adding unnecessary load to the server as it attempts to continue processing. Additionally, the root failure can easily be lost as errors pile up due to subsequent line executions.

A simple example of this can be accomplished with the following stored proc being called from a form load event.

CREATE PROCEDURE SomeErrors
AS
BEGIN

DECLARE @Zero int,
@Value int
SET @Zero = 0

SELECT @Value = 100/@Zero
SELECT 'The value is ' + @Value
END

The error bubbled up is "Divide by zero error encountered.\r\nConversion failed when converting the varchar value 'The value is ' to data type int."

As you can see both the divide by zero and the invalid casting were executed. With a simple modification, the server will report the first error and stop the batch immediately.

ALTER PROCEDURE SomeErrors
AS
BEGIN

DECLARE @Zero int,
@Value int
SET @Zero = 0
BEGIN TRY
SELECT @Value = 100/@Zero
SELECT 'The value is ' + @Value
END TRY
BEGIN CATCH
DECLARE @ErrorSeverity INT, @ErrorNumber INT, @ErrorMessage NVARCHAR(4000), @ErrorState INT
SELECT @ErrorSeverity = ERROR_SEVERITY(),
@ErrorNumber = ERROR_NUMBER(),
@ErrorMessage = ERROR_MESSAGE(),
@ErrorState = ERROR_STATE()
IF @ErrorState = 0
SET @ErrorState = 1

RAISERROR ('ERROR OCCURED:%d; %s', @ErrorSeverity, @ErrorState, @ErrorNumber, @ErrorMessage)
END CATCH
END

Now we only see the first error bubble up to the application: "ERROR OCCURED:8134; Divide by zero error encountered."

I'd love to hear what other opinions are on this matter.