Will stored proc rollback on error?
-
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,
@ErrorCode int = 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
-
TO be safer you could add save points to your code.
See "SAVE TRANSACTION" in SQL BOL
-
good idea, thanks
-
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 0RETURN
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis 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.
-
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply