T-SQL - Continue Query Execution on Errors

Question

I am using SQL Server 2012.

How to continue Query Execution if any error occurs? If in the following example 2nd query fails then rest queries didn't execute.

INSERT Schema1.[Menu] ([CompanyId], [Menu], [Role]) VALUES (5, N'Transaction', 2)
INSERT Schema2.[Menu] ([CompanyId], [Menu], [Role]) VALUES (5, N'Transaction', 2)
INSERT Schema3.[Menu] ([CompanyId], [Menu], [Role]) VALUES (5, N'Transaction', 2)
INSERT Schema4.[Menu] ([CompanyId], [Menu], [Role]) VALUES (5, N'Transaction', 2)

How to solve this?

put every insert into a try catch ?

GuidoG
– GuidoG

2016-10-07 07:18:38 +00:00
Commented Oct 7, 2016 at 7:18 — GuidoG
– GuidoG, Commented Oct 7, 2016 at 7:18

Aleksandar Matic · Accepted Answer · 2016-10-07 07:20:02Z

1

Or if you don't want to log anything you can just separate the queries by using GO

INSERT Schema1.[Menu] ([CompanyId], [Menu], [Role]) VALUES (5, N'Transaction', 2)
GO
INSERT Schema2.[Menu] ([CompanyId], [Menu], [Role]) VALUES (5, N'Transaction', 2)
GO
INSERT Schema3.[Menu] ([CompanyId], [Menu], [Role]) VALUES (5, N'Transaction', 2)
GO
INSERT Schema4.[Menu] ([CompanyId], [Menu], [Role]) VALUES (5, N'Transaction', 2)
GO

answered Oct 7, 2016 at 7:20

Aleksandar Matic

7991 gold badge9 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Anup Over a year ago

Exactly. I don't want to Log anything. I will try this.

AntDC Over a year ago

Resulting in 3 unhandled errors. Even if you don't want to log anything, try..catch will catch the errors so, albeit loosely, they are handled.

Aleksandar Matic Over a year ago

He/she just wanted to continue the query, nothing else.

AntDC · Accepted Answer · 2016-10-07 07:18:15Z

0

You could wrap each statement in a try..catch block.

That way you could log failures and no exactly waht didn't execute as expected.

See link below for try catch syntax.

https://msdn.microsoft.com/en-us/library/ms175976.aspx

answered Oct 7, 2016 at 7:18

AntDC

1,93715 silver badges23 bronze badges

Comments

GuidoG · Accepted Answer · 2016-10-07 07:21:19Z

0

try this:

begin try 
    INSERT Schema1.[Menu] ([CompanyId], [Menu], [Role]) VALUES (5, N'Transaction', 2)
end try
begin catch
     -- what you want to do in catch
end catch    

begin try 
    INSERT Schema2.[Menu] ([CompanyId], [Menu], [Role]) VALUES (5, N'Transaction', 2)
end try
begin catch
      -- what you want to do in catch
end catch    

and so on...

answered Oct 7, 2016 at 7:21

GuidoG

12.3k6 gold badges59 silver badges102 bronze badges

2 Comments

Anup Over a year ago

If i remove the catch statements & keep only try, Will it work?

GuidoG Over a year ago

no. If you start a try you have to and it with catch. The catch may be empty offcourse

this.hart · Accepted Answer · 2016-10-07 07:39:09Z

0

   -- maybe like this :)
    -- using tran catch

    BEGIN TRY
    BEGIN TRAN
     INSERT Schema1.[Menu] ([CompanyId], [Menu], [Role]) 
     VALUES (5,   N'Transaction', 2)
     INSERT Schema2.[Menu] ([CompanyId], [Menu], [Role])
    VALUES (5, N'Transaction', 2)
    INSERT Schema3.[Menu] ([CompanyId], [Menu], [Role]) 
    VALUES (5, N'Transaction', 2)
   INSERT Schema4.[Menu] ([CompanyId], [Menu], [Role]) 
   VALUES (5, N'Transaction', 2)
   COMMIT TRAN
  END TRY
   BEGIN CATCH
   ROLLBACK TRAN
  END CATCH

answered Oct 7, 2016 at 7:39

this.hart

3282 silver badges10 bronze badges

3 Comments

GuidoG Over a year ago

this will undo the succeeded inserts if just one fails. I dont see the differences with the OP original code

this.hart Over a year ago

your originals code if fails one, others can execute if not fails. and my code if only one fails or more, all is fails too...

GuidoG Over a year ago

your code will result in all inserts rollbacked if one or more fails. That is exact what the OP does NOT wants

Collectives™ on Stack Overflow

T-SQL - Continue Query Execution on Errors

4 Answers 4

3 Comments

Comments

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related