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?
asked Aug 23, 2012 at 20:48
-
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?– Aaron BertrandAug 23, 2012 at 20:52
-
3Could you check to see if a FK exists, and run a SELECT statement to check the values?– SQLRockstarAug 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.– Jay SullivanAug 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?– Aaron BertrandAug 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.– Jay SullivanAug 23, 2012 at 21:38
Add a comment
|
3 Answers
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
answered Aug 23, 2012 at 21:38
-
Thanks. I was looking for this. Is there any performance penalty of using individual TRY/CATCH block for each row?– userJun 20, 2020 at 22:58
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
answered Aug 23, 2012 at 21:23
-
1And 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
-
1If 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
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