CODEWITHSUNDEEP
c#.netsql-servert-sql
GurdeepS
GurdeepS

Reputation: 67213

Should I put error handling in T-SQL?

As a developer, error handling and try/catch is a very important part of the code I write.

However, in SQL Server Stored Procs, is it best practise to write error handling within the SP? And if one does not (which seems to be the common case), does the exception propagate to the .NET code? I ask that question as I am under the impression that T-SQL behaves like C# error handling.

And what is the best way to write error handling in T-SQL?

Upvotes: 3

Views: 386

Answers (5)

HABO
HABO

Reputation: 15816

My preference is to have the database defend itself against "bad" data. (I was once advised that I should design for 10% bad data. By a Control Engineer designing an automated machine. Oy.) As such, I try to use indexes, foreign keys, constraints and triggers to catch what I can. All access is through stored procedures, give or take the wizards playing the SSMS.

In stored procedures I tend not to worry much about a statement failing, rather that what is going on makes sense. Input validation is quite common, usually along the lines of:

-- Validate the input.
if @Serial is NULL
    begin
    rollback transaction
    RaIsError( '@Serial   cannot be   NULL .', 13, 0 )
    return
    end
set @TelephoneNumber = LTrim( RTrim( @TelephoneNumber ) )
if @TelephoneNumber is NULL or @TelephoneNumber = ''
    begin
    RaIsError( '@TelephoneNumber   must be supplied.', 13, 0 )
    return
    end

(Aside: When using the Sun god boo boo, RaIsError, the severity needs to be >=11 to get a SQL exception thrown to .NET. Maybe we could all chip in and buy Microsoft a vowel.)

Similarly, I check intermediate results as needed. For example, if I'm supposed to look up something extant and unique, but find the number of rows isn't one, then I'll bug out.

Upvotes: 1

user596075
user596075

Reputation:

It depends.

Often times if it is a very inclusive stored procedure that handles almost all of the leg work of the logic then I most definitely trap errors and handle them accordingly in that stored procedure.

But sometimes, if it is just a quick call or query I'll let the error surface to the application to have that "ease" of reading the error and handling in the application code.

Upvotes: 0

p.campbell
p.campbell

Reputation: 100567

Yes, use TRY...CATCH in your TSQL code. The exception does NOT propogate up to the client in this case.

Propogation will occur whenever you use RAISEERROR or on unhandled SQL exceptions.

BEGIN TRY
     SELECT Foo FROM MyLinkedServer.dbo.SomeTable; --what if the remote server is down?

     DECLARE @Baz uniqueidentifier  = 15/2; --oops        
END TRY
BEGIN CATCH
    SELECT @Baz = NEWID();
    ROLLBACK TRAN; 
    --whatever business logic you have for handling your exception.
END CATCH

Upvotes: 5

duffymo
duffymo

Reputation: 308743

I don't know that there's a black and white choice. There are always trade-offs.

Unless there's a clear recovery action to be taken, I don't see the point.

If the database is shared by multiple clients, perhaps it makes sense. To do otherwise would force every client to implement the error handling logic. In that case, the question would be what should be sent back to the client? Surely they deserve to know what happened and why.

The answer will depend on whether you're a DBA or a middle tier developer. Each one is likely to argue that the responsibility is best left to them.

Upvotes: 1

Dylan Smith
Dylan Smith

Reputation: 22235

I like to keep as much logic out of my SQL and in my codebase as possible. The logic involving "if this exception occurs I want to take this action", sounds like logic I'd prefer to have in my code.

So I will typically let the exception propagate up to code and deal with it there.

On a sidenote, people use try/catch far too much in my experience. Only try/catch if there's actually something you want to do differently in the case of an error. If you just want to apply generic error logging type code, let it propagate all the way to the top and deal with it in one-place there. Even worse is when people litter their code with try/catches that swallow the exceptions for no good reason. </rant>

Upvotes: 2

Related Questions