11

I'd like to be able to predict whether a DELETE will run into a constraint violation, without actually performing the delete.

What are my options for doing this? Is there a simple way to do a "dry run" of a DELETE?

5
  • Are you trying to prevent the exception for this statement alone, or are you trying to ease error handling in a larger batch that contains this delete? Aug 23, 2012 at 20:52
  • 3
    Could you check to see if a FK exists, and run a SELECT statement to check the values? Aug 23, 2012 at 21:02
  • Aaron: We need to run a batch of several DELETEs in separate transactions. If one fails, the others are already committed. (Bad design from the start, I know, but it's not my application, and it's not changing.) The best workaround at the moment sounds like doing a dry check to see if the DELETEs will fail. Aug 23, 2012 at 21:05
  • Still not sure I understand. Are you trying to let the rest of the deletes succeed, or are you trying to check up front that all of the deletes will succeed, or none of them should? Aug 23, 2012 at 21:22
  • Aaron: Sorry I didn't make it clear, but yes, I'm trying to make sure they all succeed, or none of them succeed. Aug 23, 2012 at 21:38

3 Answers 3

26

If your goal is to process all deletes only if they all succeed, why not just use TRY/CATCH:

BEGIN TRANSACTION;
BEGIN TRY
  DELETE #1;
  DELETE #2;
  DELETE #3;
  COMMIT TRANSACTION;
END TRY
BEGIN CATCH
  ROLLBACK TRANSACTION;
END CATCH

If the goal is to allow all successful deletes to succeed even if one or more will fail, then you can use individual TRY/CATCH, e.g.

BEGIN TRY
  DELETE #1;
END TRY
BEGIN CATCH
  PRINT 1;
END CATCH

BEGIN TRY
  DELETE #2;
END TRY
BEGIN CATCH
  PRINT 1;
END CATCH
1
  • Thanks. I was looking for this. Is there any performance penalty of using individual TRY/CATCH block for each row?
    – user
    Jun 20, 2020 at 22:58
6

One option is to begin a transaction, run your delete, and then always rollback:

begin tran

delete Table1 where col1 = 1

-- Test whether it is there
select * from Table1 where col1 = 1

rollback tran

-- Confirm that it is still there
select * from Table1 where col1 = 1
5
  • 1
    And if the delete succeeds, run it again? What if the delete is very expensive? And if the delete fails, what then? You've done one delete and two selects. How do I decide to move on the next delete or not? Aug 23, 2012 at 21:52
  • 1
    If those are part of the requirements, then they should be handled. This answer is in regard to a "simple 'dry run'". Aug 23, 2012 at 22:27
  • Well they weren't when you first submitted your answer, but they have been clarified now. Aug 23, 2012 at 22:28
  • 4
    @GaTechThomas Aaron contributes a lot, so he is sometimes brief, but I am certain his intention was not to be aggressive. I discussed this in The Heap and I'd be grateful for the chance to do so with you as well. Aug 24, 2012 at 14:50
  • 1
    @JackDouglas I have read The Heap comments that you reference and understand the point. The community comments were reasonable except for the part where I was called a clown for pointing out his aggression. I don't understand how I am the one who was seen as aggressive. I posted a legitimate answer to the question as it was posed at the time. It did not ask for production quality - sometimes you just need quick and easy. So on my answer I get pointed questions. The appearance was that he was denigrating my answer so that his would be picked. Should we take this thread elsewhere? Sep 11, 2012 at 16:59
0

I would like to improve the solution provided by Aaron Bertrand with some code, in case you want to try to add any element of a table, managing the exceptions to ignore fails or also stop the process afters errors.

This one will select the records from the table and then tries to delete them without exceptions:

DECLARE @MaxErrors INT
SET @MaxErrors = 5;    // Setting 0 will stop process after the first error!

SELECT
    [Id]
    , ROW_NUMBER() OVER (ORDER BY Id ASC) AS [Index]
INTO #DeletingItems
FROM myTable

DECLARE @Current INT, @Max INT, @Id INT, @TotErrors INT
SELECT
    @Current = 1
    , @TotErrors = 0
    , @Max = MAX([Index])
FROM #DeletingTable

WHILE @Current <= @Max
BEGIN
    SELECT
        @Id = [Id]
    FROM #DeletingItems
    WHERE
        [Index] = @Index;

    BEGIN TRANSACTION;    
    BEGIN TRY    
        DELETE FROM myTable WHERE [Id] = @Id;

        COMMIT TRANSACTION;    
    END TRY    
    BEGIN CATCH    
        ROLLBACK TRANSACTION;

        SET @TotErrors = @TotErrors + 1;

        IF @TotErrors > @MaxErrors
            BREAK;
    END CATCH

    SET @Current = @Current + 1;
END
1
  • 1
    Why? How is this an improvement over the accepted answer? Jul 12, 2015 at 0:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.