Continue processing stored procedure, when a failure occurs...

All,
Is there a way, in SQL Server 6.5, to continue processing within a stored procedure even though an error occurs? An example I am inserting records into a temp table within a stored procedure, and there may be duplicate UNIQUE keys, I simply want the procedure to continue inserting records ignoring the failure.

Thank you,
Scott Kolek
Development Manager

SKM Software
http://www.skm-software.com

Some errors will break and abort a batch and som will let you continue.
I believe that a unique index violation is the breaking kind and will abort
the batch. Only way to get around it is to check for existence of the key
before insert, and skip if it exists.

There are some options to the SET statement that affects transaction abort behaviour,
but I don't have BOL at hand, so I can't tell you if they will make any difference.

/Kenneth

------------
Scott Kolek at 4/8/99 12:43:35 PM

All,
Is there a way, in SQL Server 6.5, to continue processing within a stored procedure even though an error occurs? An example I am inserting records into a temp table within a stored procedure, and there may be duplicate UNIQUE keys, I simply want the procedure to continue inserting records ignoring the failure.

Thank you,
Scott Kolek
Development Manager

SKM Software
http://www.skm-software.com

if you 're trying to do insert into temp table, then why do you get unique constraint violation error?...unless you created index(s) on that temp table prior to insert, then you can use IGNORE_DUP_KEY (for unique clustered/nonclustered index(s)) ...



------------
Kenneth Wilhelmsson at 4/8/99 5:25:21 PM

Some errors will break and abort a batch and som will let you continue.
I believe that a unique index violation is the breaking kind and will abort
the batch. Only way to get around it is to check for existence of the key
before insert, and skip if it exists.

There are some options to the SET statement that affects transaction abort behaviour,
but I don't have BOL at hand, so I can't tell you if they will make any difference.

/Kenneth

------------
Scott Kolek at 4/8/99 12:43:35 PM

All,
Is there a way, in SQL Server 6.5, to continue processing within a stored procedure even though an error occurs? An example I am inserting records into a temp table within a stored procedure, and there may be duplicate UNIQUE keys, I simply want the procedure to continue inserting records ignoring the failure.

Thank you,
Scott Kolek
Development Manager

SKM Software
http://www.skm-software.com

Scott,

Use the SELECT @@ERROR variable to capture the error. This works similiarly to the error handling in VB. If you don't capture the error, it will usually cause the whole procedure to fail. Here is an example piece of code:

CREATE PROCEDURE Test_Insert

AS

INSERT INTO tbltest
(column1, column2)
VALUES
(10000, 20000)

SELECT @@ERROR

SELECT *
FROM tblTest

GO

If the INSERT statement causes an ERROR, the ERROR will be captured by the SELECT @@ERROR and the proc will continue to work (On most ERRORS). I was able to avoid even a PK violation with an INSERT.

If you need some of the records to go in and the one that caused the violation to be saved... You need to use a CURSOR and row-process each record and check the error for success. If an error occured, write that record to a temp/permanent table for manual intervention.

Hope this has helped. If you need more info...email me, I'll be glad to help!

Richard Martin, MCSE, MCSD, MCT, MCP



------------
Scott Kolek at 4/8/99 12:43:35 PM

All,
Is there a way, in SQL Server 6.5, to continue processing within a stored procedure even though an error occurs? An example I am inserting records into a temp table within a stored procedure, and there may be duplicate UNIQUE keys, I simply want the procedure to continue inserting records ignoring the failure.

Thank you,
Scott Kolek
Development Manager

SKM Software
http://www.skm-software.com