Pit of despair: Error handling in SQL Server is seriously broken

 

Life is easy:

foreach (KeyValuePair<string, string> criterion in criteria)

{

    float value;

    //if (!float.TryParse(criterion.Value, out value))

    //{

    //    value = defaultValue;

    //}

    try

    {

        value = (float)Convert.ChangeType(criterion.Value, typeof(float), CultureInfo.InvariantCulture);

    }

    catch (FormatException)

    {

        value = defaultValue;

    }

 

    converted.Add(criterion.Key, value);

}

It could be even luxurious if we wouldn’t have to deal with Transact-SQL type conversion and Error Handling.

Errors in Transact-SQL code can be processed by using a TRY…CATCH construct similar to the exception-handling features of the Microsoft Visual C++ and Microsoft Visual C# languages.

That can possibly only be written by someone with a kinky approach to exception-handling. If anything, it might be somewhat inspired by Microsoft Basic.

 

DECLARE @Criteria TABLE (

    [Name] nvarchar(200) NOT NULL,

    Value nvarchar(400) NOT NULL,

    NumValue float CHECK (0 < COALESCE(NumValue, 1)),

    PRIMARY KEY ([Name])

);

 

BEGIN TRANSACTION;

 

INSERT INTO @Criteria ([Name], Value) VALUES (‘Pass’, ‘4.2’);

–INSERT INTO @Criteria ([Name], Value) VALUES (‘Fail’, ‘4;2’);

INSERT INTO @Criteria ([Name], Value) VALUES (‘Other’, ‘0’);

 

COMMIT TRANSACTION;

 

BEGIN TRANSACTION;

BEGIN TRY

    UPDATE @Criteria

    SET NumValue = CAST(Value AS float);

    COMMIT TRANSACTION;

END TRY

BEGIN CATCH

    IF @@TRANCOUNT > 0

    BEGIN

        ROLLBACK TRANSACTION;               

    END

 

    IF ERROR_NUMBER() <> 8114

    BEGIN

        EXEC usp_RethrowError;           

    END

END CATCH

Msg 50000, Level 16, State 1, Procedure usp_RethrowError, Line 31
Error 547, Level 16, State 0, Procedure sp_Test, Line 27, Message: The UPDATE statement conflicted with the CHECK constraint "CK__#489AC854__NumVa__4A8310C6". The conflict occurred in database "tempdb", table "@Criteria".

Foomp! We just lost the error context. All we got is an error message that appears to look like the original error.

Nice. This renders all subsequent, decoupled, generic, pluggable, configurable or otherwise smart error handling pointless.

SQL Server traps us in the Pit of Despair.

You can’t filter errors exceptions.

You can’t re-throw errors, because RAISERROR doesn’t let you use an arbitrary msg_id.

So, what is it that we learn from that?

Advertisements
This entry was posted in Coding Horror. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s