Will stored proc rollback on error?

Question

Post reply

Will stored proc rollback on error?

sqljean

Old Hand

Points: 318
More actions
March 26, 2004 at 12:54 pm

#101938

A colleage warned me that if my mega-delete proc encounters an error near the end, it will rollback all of the work done up to that point. This rollback would create an unacceptable performance hit on our database(s). But I've tried several ways to make the proc fail mid-stream, such as using RAISERROR, adding a step that would cause a primary key violation, etc., and I see that the work done up to the point of the error was committed, which is exactly what I want. Am I missing something here? Can I safely use a proc that does many deletes and not worry about a big rollback?
The reason I'm putting so much work into a single proc is so I can create multiple versions of the proc, each running against a different database with different schema. Then my Java application can call dbo.MyProc, and based on a properties setting, it will point to the correct database. That way I can use a single Java app, running in multiple instances with different properties, to archive data out of multiple diverse databases on different servers.
Thanks,
-Jean
CREATE PROCEDURE dbo.MyProc
(
@MyID int,
@errorcodeint = NULL output
)
AS
SET NOCOUNT ON
DELETE FROM .dbo.TableA WHERE ID = @MyID
IF @@ERROR <> 0
BEGIN
SELECT @errorcode = @@ERROR
SET NOCOUNT OFF
RETURN
END
DELETE FROM .dbo.TableB WHERE ID = @MyID
IF @@ERROR <> 0
BEGIN
SELECT @errorcode = @@ERROR
SET NOCOUNT OFF
RETURN
END
/*
continue this pattern to delete from about 30 tables,
in child-parent order to avoid foreign key violation
*/
SELECT @errorcode = @@ERROR
SET NOCOUNT OFF
GO

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

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

Antares686 SSC Guru Points: 125444 More actions · Answer 1

TO be safer you could add save points to your code.

See "SAVE TRANSACTION" in SQL BOL

sqljean Old Hand Points: 318 More actions · Answer 2

sqljean

Old Hand

Points: 318

March 26, 2004 at 3:22 pm

#500768

good idea, thanks

gljjr SSChampion Points: 10080 More actions · Answer 3

If this is a very large table you could look into using the SET ROWCOUNT syntax and then loop through until you no longer delete records. Then in the loop you can commit your transaction as needed. NOTE that the rollback will only do the last N affected records and not the whole delete.

IE: -- Please note that this has not been tested in any way. I wrote it up on the fly

DECLARE @NumRows int, @iErr intSET @NumRows = 1SET ROWCOUNT 5000WHILE @NumRows > 0    BEGIN        BEGIN TRAN        DELETE FROM .dbo.TableA WHERE ID = @MyID                SELECT @iErr = @@ERROR, @NumRows = @@ROWCOUNT        IF @iErr != 0            BEGIN                ROLLBACK TRAN                SET ROWCOUNT 0                RAISERROR('Error During Delete', 1, 16)                RETURN            END        COMMIT TRAN    ENDSET ROWCOUNT 0

RETURN

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

Razvan Socol SSCarpal Tunnel Points: 4811 More actions · Answer 4

For everything you want to know about error handling in T-SQL, read the following excellent article, by Erland Sommarskog:

http://www.algonet.se/~sommar/error-handling-II.html

Razvan