3

Is there a way to abort a stored procedure in SQL Server if a delete statement cannot be successfully executed due to a foreign key constraint violation? By default, the procedure seems to ignore the error and go on to the next statement.

For other types of error (e.g. deleting from a table that does not exist) however, the procedure aborts.

Example procedure:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE  [dbo].[TestSP]
AS
BEGIN
    SET NOCOUNT ON;

    print 'BEFORE';

    DELETE FROM ExistingWithConstraints;
    print 'AFTER DELETE ExistingWithConstraints';

    DELETE FROM NonExisting;
    print 'AFTER DELETE NonExisting';
END

produces output (note that the last message above is not printed):

BEFORE
<snip constraint violation error message>
AFTER DELETE ExistingWithConstraints    
<snip invalid object name error message>
ehsany
  • 33
  • 3

2 Answers2

3

Use a transaction and proper error handling

CREATE PROCEDURE  [dbo].[TestSP]
AS
SET XACT_ABORT, NOCOUNT ON
DECLARE @starttrancount int

BEGIN TRY
    SELECT @starttrancount = @@TRANCOUNT

    IF @starttrancount = 0
        BEGIN TRANSACTION

    DELETE FROM ExistingWithConstraints;
    DELETE FROM NonExisting;

    IF @starttrancount = 0 
        COMMIT TRANSACTION
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 AND @starttrancount = 0 
        ROLLBACK TRANSACTION
    RAISERROR [rethrow caught error using @ErrorNumber, @ErrorMessage, etc]
END CATCH
GO

This is based on my answer here: Nested stored procedures containing TRY CATCH ROLLBACK pattern?

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
  • If `@@TRANCOUNT > 0` you could use savepoints to rollback in err handler modifications done by `TestSP` only -- a more universal pattern we are using here. – wqw Nov 03 '11 at 13:09
  • @wqw: I don't use savepoints myself: a DB call should be atomic. I you follow my link you'll see another template from Remus using these. You also have to careful with use XACT_ABORT which dooms transactions – gbn Nov 03 '11 at 14:16
-1

The execution carries on to allow for you checking the @@ERROR standard variable for problems. A more modern way to do things though it to use the TRY..CATCH blocks added to SQL in 2005:

http://msdn.microsoft.com/en-us/library/ms179296.aspx

Jon Egerton
  • 40,401
  • 11
  • 97
  • 129