trapping stored procedure errors

trapping stored procedure errors

Post by Keith Fische » Tue, 21 Dec 1999 04:00:00



I have a stored procedure that will sometimes try to insert a duplicate
record into a table.  When this happens you get the expected PK
violation errors.  How do I go about trapping them and continuing the
while loop in which they ocur?  If I don't trap them then my Access
front end pops up a dialog with all the errors. Even though the sp
completes.  The code is below, it randomly selects a record from one
table and puts it in another.  It does this until the temp table has 15%
of the first tables entries.  I get the same errors if I run this in the
Query Analy.  Would it be wiser to check the temp table first to see if
it's a duplicate, and if not, insert it, thus avoiding the error
message?  (btw, The random code was borrowed from the newsgroup and I
haven't fully tested it to be sure how "random" it really is.)

Thanks
Keith



table

represented as a decimal

to table so far





-- Sets the percentage of list to draw at 15%

--  Need to know how many records are in the pool to figure out how many
we should select

-- Calculate how many records we need to draw from the pool (percantage
of records) cast to an INT.

--Init counter to 1.  Will increment everytime we get a sucessful
addition to temp table

-- Define and Open cursor to cruise through table in either direction
(include all records)
declare Random_Cursor scroll cursor for select * from Bargaining
open Random_Cursor


insert into temp table
--  will reject if it's already been added (PK duplicate).  So don't
increment counter and loop
-- again,

 begin

Bargaining))



  INSERT INTO Bargaining_temp


 end

-- Close cursor and clean up
close Random_cursor
deallocate Random_Cursor

 
 
 

trapping stored procedure errors

Post by Greg Druia » Tue, 21 Dec 1999 04:00:00


I've kind of butchered your code, but is this what you're after?


begin
...
BEGIN TRANSACTION
INSERT ...

   BEGIN
   ROLLBACK TRANSACTION
   GOTO continue_block
   END
COMMIT TRANSACTION

continue_block:
...
end


> I have a stored procedure that will sometimes try to insert a duplicate
> record into a table.  When this happens you get the expected PK
> violation errors.  How do I go about trapping them and continuing the
> while loop in which they ocur?  If I don't trap them then my Access
> front end pops up a dialog with all the errors. Even though the sp
> completes.  The code is below, it randomly selects a record from one
> table and puts it in another.  It does this until the temp table has 15%
> of the first tables entries.  I get the same errors if I run this in the
> Query Analy.  Would it be wiser to check the temp table first to see if
> it's a duplicate, and if not, insert it, thus avoiding the error
> message?  (btw, The random code was borrowed from the newsgroup and I
> haven't fully tested it to be sure how "random" it really is.)

> Thanks
> Keith



> table

> represented as a decimal

> to table so far





> -- Sets the percentage of list to draw at 15%

> --  Need to know how many records are in the pool to figure out how many
> we should select

> -- Calculate how many records we need to draw from the pool (percantage
> of records) cast to an INT.

> --Init counter to 1.  Will increment everytime we get a sucessful
> addition to temp table

> -- Define and Open cursor to cruise through table in either direction
> (include all records)
> declare Random_Cursor scroll cursor for select * from Bargaining
> open Random_Cursor


> insert into temp table
> --  will reject if it's already been added (PK duplicate).  So don't
> increment counter and loop
> -- again,

>  begin

> Bargaining))



>   INSERT INTO Bargaining_temp


>  end

> -- Close cursor and clean up
> close Random_cursor
> deallocate Random_Cursor


 
 
 

1. Trapping Stored Procedure Errors in ADO

Hi,

I am accessing Stored Procedures on Sybase from VB 6.0 using ADO.  If
there any errors in the stored procedure, then I do the following

  raiserror <error num> <error message>
  return -1

I would like to get the error message and display it to the user.  Is
there any way of achieving this?

Thanks

Sent via Deja.com http://www.deja.com/
Before you buy.

2. Recovery plan for Failover or Switchover to alternate site

3. Trapping Errors Raised Within Stored Procedures

4. Connecting to Informix-Turbo 3.1 via ODBC

5. Trapping Errors in Stored Procedures

6. allowing user to choose active index

7. Trapping SQL Server stored procedure error from Delphi

8. Lock Master

9. Stored Procedure error trap

10. Trapping Errors within a Stored Procedure

11. Stored Procedure error trapping advice

12. DTS with stored procedures / error trapping

13. Trapping Errors within a Stored Procedure