CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
Anup
Anup

Reputation: 9738

T-SQL - Continue Query Execution on Errors

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?

Upvotes: 1

Views: 1415

Answers (4)

this.hart
this.hart

Reputation: 328

   -- 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

Upvotes: 0

GuidoG
GuidoG

Reputation: 12014

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...

Upvotes: 0

Aleksandar Matic
Aleksandar Matic

Reputation: 799

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

Upvotes: 1

AntDC
AntDC

Reputation: 1917

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

Upvotes: 0

Related Questions