Error handling

Question

Error handling

Sue-651097

Say Hey Kid

Points: 675
More actions
November 10, 2008 at 5:50 am

#94089

hi,
how can i implement an error handler in my sql code?
i have a proc with many different string functions on a text as input parameter.
normally functions are working, but sometimes there is a record with wrong data and then the proc failed.
what i need is an error handling that the proc doesn't fail but delete this record.
i tried transactions, xaxt_abort and try catch in 2008 (but destination server is 2K). although the severity code is only 16, the transaction doesn't work and the statement stops with a failure after the substring.
error code 537 invalid lenght parameter to left or substring function.
begin tran
select substring('xyz',2,-1)
if @@error = 0
commit tran
else
begin
select wrong
rollback tran
delete......
end
thanks in advance,
sue
Susanne

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

with SQL 2005 you can use TRY CATCH blocks for error handling, put the code for rolling back your changes in the CATCH block

http://msdn.microsoft.com/en-us/library/ms175976.aspx

Sue-651097 Say Hey Kid Points: 675 More actions · Answer 2

thanks.

yes, i know this and i already tried it as i mentioned.

but 1) the desintation server is sql2000 and 2) it doesn'work 😉

it works with example from knowledge base (0 division) but not with my problem.

begin try

begin tran

select LEFT('xyz',-1)

commit tran

end try

begin catch

rollback tran

end catch

Susanne

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 3

You don't really have any options in SQL Server 2000. The best you can do is check after each statement for an error value and use a GO TO to try deal with it. Stinks, but there it is. I wrote an article [/url]on error trapping that includes a bunch of 2000 functionality. It won't help what you're running into, but it will outline what you can do.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Sue-651097 Say Hey Kid Points: 675 More actions · Answer 4

thanks. i will check out the articel a little later because it is rather long ;))

but is it right that i only can find out that an error occurs and go on with this:

create proc test

@len int

as

select left('hans',@len)

go

declare @err as int

exec @err = test -3

select @err

so i can only check if the procedure failed but not the string funtions?

the problem is that i have a procedure with an insert statement an this insert raises a trigger in which the string functions are (and failed sometimes). how can i check if the trigger has failed?

Susanne