How to wrap queries so if one fails they all fail

Question

How to wrap queries so if one fails they all fail

loki1049

SSCrazy

Points: 2065
More actions
December 21, 2010 at 12:52 pm

#229725

I have a series of Update, delete, and select queries which I am using to set up default data in a data base. First I remove old set up data and delete from appropriate tables, then I insert my new data. Is there a way to wrap all of the data in one query and make it fail entirely if one part fails?
The main issue I am having is that I delete from a couple tables and then try to delete from another that has a foreign key relations and get a foreign key error which cuts my query in half so that only half of it executes. I am using a table variable to temporarily hold data that needs to be deleted, and if the query fails half way through I lose what data I needed to delete. This leaves me with orphaned data that I have to go through table by table and delete.
I know my query should be designed to take into account FK's but I can't test it as one entire script without the fear that I may have forgot something and hence caused myself a huge deal of work.

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

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

For your permanent and temporary tables (not table variables), you can wrap all of this up with a begin transaction statement, and with the use of try/catch you can decide whether to use a commit transaction or rollback transaction statement.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

loki1049 SSCrazy Points: 2065 More actions · Answer 2

WayneS (12/21/2010)
For your permanent and temporary tables (not table variables), you can wrap all of this up with a begin transaction statement, and with the use of try/catch you can decide whether to use a commit transaction or rollback transaction statement.

Cool that is what I was looking for, thanks a lot I will look into this.

loki1049 SSCrazy Points: 2065 More actions · Answer 3

Just incase anyone else sees this I found a link with an example that seems to describe this pretty well.

TransactionLink <-- Click!