Dynamic Error Handling in Procedure
-
I have an application that the users use for some dynamic conversion routines.
Basicly I build dynamic sql, and EXECUTE @MY_SQL. This works 99% of the time.
The problem I have is when some errors occur the procedure stops executing, and dosent go thru my nice error handling section. Anyone have any ideas on how to get the procedure to trap the error and not stop.
see the following examples and output
****************************************************
create procedure test_all
as
begin
declare @return_status int,
@my_sql varchar(8000),
@return_code int ,
@error_text varchar(256)
set @my_sql = 'update employee set bonus_amount = salary/0'
SET XACT_ABORT off
begin transaction failover
EXECUTE (@my_sql )
set @return_code = @@error
if @return_code = 0
commit transaction failover
else
begin
rollback transaction failover
print 'return_code = ' + convert( varchar(10), @return_code)
select @error_text = description from master..sysmessages
where error = @return_code
print @error_text
end
SET XACT_ABORT on
end
end
****************************************************
Now this one works great and generates the following correct messages....because the denominator = zero.
Server: Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
The statement has been terminated.
return_code = 8134
Divide by zero error encountered.
Excellent......the problem I am having is when the data is bad.....
****************************************************
create procedure test_all
as
begin
declare @return_status int,
@my_sql varchar(8000),
@return_code int ,
@error_text varchar(256)
set @my_sql = 'update employee set bonus_amount = ''abc'' '
SET XACT_ABORT off
begin transaction failover
EXECUTE (@my_sql )
set @return_code = @@error
if @return_code = 0
commit transaction failover
else
begin
rollback transaction failover
print 'return_code = ' + convert( varchar(10), @return_code)
select @error_text = description from master..sysmessages
where error = @return_code
print @error_text
end
SET XACT_ABORT on
end
end
****************************************************
Notice the change....the numeric will get a text value...generating the following:
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
################################################
the problem is that the procedure ends at this point.......not going into the error handling and causing my users to get these UGLY messages and not the nice formated ones that I send them back.
Anyone have any ideas on how to trap ALL dynamic SQL errors?
thanks in advance
Eric
-
more research has uncovered the following which answers my questions on what causes aborts..... While my example is catching STATEMENT errors, the BATCH errors are causing the procedures to fail. Looks like nothing I can do about it.
Error type will abend
Duplicate primary key. Statement NOT NULL violation.. Statement Violation of CHECK or FOREIGN KEY constraint. Statement Most conversion errors, for instance conversion of non-numeric string to a numeric value. BATCH Attempt to execute non-existing stored procedure. Statement Missing or superfluous parameter to stored procedure to a procedure with parameters. Statement Superfluous parameter to a parameterless stored procedure. BATCH Exceeding the maximum nesting-level of stored procedures, triggers and functions. BATCH Being selected as a deadlock victim. BATCH Permission denied to table or stored procedure. Statement ROLLBACK or COMMIT without any active transaction. Statement Mismatch in number of columns in insert/exec BATCH Declaration of an existing cursor Statement Column mismatch between cursor declaration and FETCH statement. Statement. Running out of space for data file or transaction log. BATCH At least now I know....
Eric
-
EricP37 says:
Now this one works great and generates the following correct messages....because the denominator = zero.
Server: Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
The statement has been terminated.
return_code = 8134
Divide by zero error encountered.
Excellent......the problem I am having is when the data is bad.....
This is a very interesting return, as Dynamic SQL instantiates its own SPID; hence it is a new SPID within an existing SPID. Yet, you are obviously able to capture the error.
You do DECLARE @my_sql to be varchar(8000), hence you should be able to ROLLBACK the TRANSACTION, (which you name - I like that) within the Dynamic SQL. Have you tried that approach?
I wasn't born stupid - I had to study.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply