Error handling in TSQL -- sql server programming

Sam
10/6/2003 1:52:37 AM

hi!
I have tried your method. Doesn't help.
I am still getting messages like:
====================
transaction starts
(10 row(s) affected)
(12 row(s) affected)
(13 row(s) affected)
Server: Msg 208, Level 16, State 1, Line 9
Invalid object name 'manageril1'.
=====================
the procedure did not even reach the error_handler step:

begin tran
print 'transaction starts'
insert into profile select * from #profins
if @@error !=0 GOTO error_handler
insert into prof_compo select * from #compoins
if @@error !=0 GOTO error_handler
insert into apps_user select * from #appluserins
if @@error !=0 GOTO error_handler
insert into manager select * from #manageril1
if @@error !=0 GOTO error_handler

COMMIT TRAN
RETURN

error_handler:
print 'before rollback'
rollback tran
print 'rollback'

return
go
[quoted text, click to view]

Jacco Schalkwijk
10/6/2003 10:08:08 AM

Hi Sam,

Compile errors, like for example the fact that a (temporary) table doesn't
exists, will terminate the batch and any statement after the statement that
caused the error will not be executed, so your errorhandler won't run. You
can check for the existence of the temporary table before you do the insert
with
IF OBJECT_ID('tempdb..#manageril1') IS NOT NULL
insert into manager select * from #manageril1
ELSE --Oops no table!
GOTO error_handler

But basically it is a big fat bug in your code that the table doesn't exist,
and I am of the opinion that in a database you should test for bugs and not
go out of your way to write error handling code for them. The only thing
error handling code in this specific case can do is raise a message, you
can't recover from it.

--
Jacco Schalkwijk
SQL Server MVP

[quoted text, click to view]

Louis Davidson
10/6/2003 10:21:29 AM

One note, it is not important that the object exists when the proc is
compiled. The following compiles nicely:

create procedure [there ain't no dang table named #fleck!]
as
select * from #fleck
go

Compiles nicely. Try to run it:

exec [there ain't no dang table named #fleck!]

you get the following error:

Server: Msg 208, Level 16, State 1, Procedure there ain't no dang table
named #fleck!, Line 3
Invalid object name '#fleck'.

--
----------------------------------------------------------------------------
-----------
Louis Davidson (drsql@hotmail.com)
Compass Technology Management

Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266

Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)

[quoted text, click to view]

Jacco Schalkwijk
10/6/2003 11:46:16 AM

[quoted text, click to view]

Yes, true. I intended "compile error" to mean a class of errors caused by
compilation/syntax issues, as opposed to constraint violations etc. Which
isn't really a good classification either, because some syntax errors have a
different behaviour. We should probably call them "deferred name resolution
errors" is that is the root cause of all evil here.

Looking at Erland's article I also noticed that a deferred name resolution
error terminates the scope not the batch (ok, there's a limit to the number
of inconsistencies I can remember ;-)), so if you call the stored procedure
with a possible deferred name resolution error from another stored procedure
you can handle the error in the outer stored procedure, something which you
can't do with a batch terminating error.

See this example I adjusted from the article for the difference:

CREATE PROCEDURE inner_sp @productid int AS

CREATE TABLE #temp (orderid int NOT NULL,
orderdate datetime NOT NULL)

PRINT 'This prints.'
BEGIN TRANSACTION

INSERT #temp (orderid, orderdate)
SELECT o.OrderID, o.OrderDate
FROM Orders
WHERE EXISTS (SELECT *
FROM [Order Details] od
WHERE od.OrderID = o.OrderID
AND od.ProductID = @productid)

COMMIT TRANSACTION
PRINT 'This does not print.'
go
CREATE PROCEDURE inner_sp_2 AS
DECLARE @i INT
SET @i = 'a'
GO

CREATE PROCEDURE outer_sp AS

DECLARE @ret int
SET @ret = 4711
EXEC @ret = inner_sp 76

PRINT '1: @@error is ' + ltrim(str(@@error)) + '.'
PRINT '1: @@trancount is ' + ltrim(str(@@trancount)) + '.'
PRINT '1: @ret ' + coalesce(ltrim(str(@ret)), 'NULL') + '.'
IF @@trancount > 0 ROLLBACK TRANSACTION

EXEC @ret = inner_sp_2
PRINT '2: @@error is ' + ltrim(str(@@error)) + '.'
PRINT '2: @@trancount is ' + ltrim(str(@@trancount)) + '.'
PRINT '2: @ret ' + coalesce(ltrim(str(@ret)), 'NULL') + '.'
IF @@trancount > 0 ROLLBACK TRANSACTION
go
EXEC outer_sp
go

DROP PROCEDURE inner_sp, inner_sp_2, outer_sp

--
Jacco Schalkwijk
SQL Server MVP

[quoted text, click to view]

Jacco Schalkwijk
10/6/2003 1:33:20 PM

Totally agree. If only SET XACT_ABORT worked consistently I could live
(mostly) with the rest. At least you can then use transactions everywhere to
create a baseline that prevents any problems with the data integrity and
leaves the database in a consistent state. Not that the programmers who
write code against the database are going to be happy, because they will
have to write all the error handling code, but still better than having to
fix invalid data.

Microsoft haven't been saying a lot in public about enhancements in T-SQL,
but they have mentioned structured exception handling. And I assume they
have realised you can't have structured exception handling without
consistent exception behaviour.

--
Jacco Schalkwijk
SQL Server MVP

[quoted text, click to view]

Greg Linwood
10/6/2003 4:47:43 PM

Hi Sam.

You have to check @@error after EVERY statement in T-SQL.

Try this:

begin tran
print 'Here goes the transactions'
insert into profile select * from #profins
if @@error !=0 goto error_handler
insert into prof_compo select * from #compoins
if @@error !=0 goto error_handler
insert into apps_user select * from #appluserins
if @@error !=0 goto error_handler
insert into manager select * from #manageril1
if @@error !=0 goto error_handler

commit tran
return

error_handler:
rollback tran
print 'rollback'

return
go

You can also use SET XACT_ABORT ON to auto-rollback a transaction on any
error, but be aware - there are pit-falls to using this & documentation is
fairly skinny on how it all works.

I strongly suggest you read two articles on T-SQL error handling written by
SQL MVP Erland Sommarskog a few months back. T-SQL error handling is far
more complex than it first looks & you'd do very well to read these articles
at:
http://www.algonet.se/~sommar/

HTH

Regards,
Greg Linwood
SQL Server MVP

[quoted text, click to view]

Jacco Schalkwijk
10/6/2003 5:19:21 PM

Hi Louis,

The stored procedure _parses_ nicely. That's all that happens when you
create a stored procedure. _Compiling_ only happens when you execute it and
a execution plan is created.

Greg wasn't completely correct either though, I just traced the script at
the end of the post and dropping the temporary table causes the stored
procedure to be recompiled, so you can't have a compiled execution plan
based on a non existing table, and the error you get is a compile error, not
a runtime error .

CREATE TABLE #fleck (f int)
GO
create procedure [there ain't no dang table named #fleck!]
as
select * from #fleck
go

-- Parses nicely.

-- Try to run it:
exec [there ain't no dang table named #fleck!]
GO
DROP TABLE #fleck
GO
-- procedure gets recompiled
exec [there ain't no dang table named #fleck!]
GO

DROP PROCEDURE [there ain't no dang table named #fleck!]

--
Jacco Schalkwijk
SQL Server MVP

[quoted text, click to view]

Greg Linwood
10/6/2003 7:45:08 PM

Might not actually be a compile error if the stored proc was compiled when
the table existed..

I didn't notice it was an object not found type error in your original post,
sorry.. This error is about the most difficult to handle. It never was such
a big problem in the early days - it became a problem in SQL 7 when deferred
name resolution was introduced (although I'm not 100% sure this is the
cause).

Basically, as Jacco says - there is no way you can handle an object not
found error in T-SQL when a table is referenced that doesn't exist because
execution ceases unconditionally, so any error handling you have after that
line simply won't get executed. Additionally, if you have a transaction
open, it will be left open - even if you use SET XACT_ABORT ON which is
another thing to look out for. Generally though - if you want to use error
handling in T-SQL, you need to check @@error after every line..

I've always personally been against sticking big fat, branching checks for
table existance in the code path because it causes an unfortunate
concurrency problem on sysobjects (during the object_id() check). It also
contributes to making code seem more like spaghetti code which complicates
maintenance. Personally, I (like many others) wish the exception management
was better & am looking forward to something better in the next release.

My best answer to solving this problem is to put your defensive code into
the application layer - ie checking for object existance if need be there.
My rational for this is that the application layer needs to be cleaning up
transactions left over by object not found type errors anyway..

HTH

Regards,
Greg Linwood
SQL Server MVP

[quoted text, click to view]

Greg Linwood
10/6/2003 9:16:07 PM

Your adjustment to the example stored procs highlights the difference
between scope & batch termination quite well..

Basically, I regard exception management in SQL2K as a bad experience for a
number of reasons. I've observed very experienced SQL Server professionals
have difficulty grasping how it works (me included) because it seems to not
make sense in so many respects & it just isn't documented anywhere near well
enough. Simple things like constraint violations behaving differently inside
triggers than outside triggers, severity levels seeming inconsistent,
transaction management & when / where set xact_abort can be expected to work
/ not are just all too confusing.

What you need with exception management is simplicity & reliability, not
complex sets of rules about what circumstances this behaviour occurs & that
behaviour occurs. Programmers have enough on their mind without having to
worry about such a complex set of exception management rules..

The good news, of course, is that Microsoft is paying attention & seems to
be doing something about it. I'm hoping for something better in the near
future!

Regards,
Greg Linwood
SQL Server MVP

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : Error handling in TSQL