Dec 17, 2018 From SQL Server (not sure about other RDBMS), You can call multiple stored procedures inside a transaction. BEGIN TRAN EXEC StoredProc1 EXEC StoredProc2 COMMIT TRAN You may want to add a return code to the stored proc to check if you should run stored proc 2 if stored proc 1 failed. EDIT: To check a return code you can do something like the. APPLIES TO: SQL Server (starting with 2008) Azure SQL Database Azure SQL Data Warehouse Parallel Data Warehouse. Creates a Transact-SQL or common language runtime (CLR) stored procedure in SQL Server, Azure SQL Database, Azure SQL Data Warehouse and Parallel Data Warehouse. A SQL stored procedure (SP) is a collection SQL statements and sql command logic, which is compiled and stored on the database. Stored procedues in SQL allows us to create SQL queries to be stored and executed on the server.
When developing data-driven software, it is often vital that you manage transactions to ensure that unexpected errors do not corrupt the database. Transactions allow you to group sets of related database calls in a single, logical unit-of-work.
Transaction Sql Server Stored Procedure Tutorial
The classic example of debiting one bank account, then crediting another, is often used to illustrate the importance of transactions. If the process blows up somewhere in the middle, you want everything put back as it was initially. It is the 'all or nothing' principle.For Microsoft developers who write applications against a SQL Server 2005 database, it is common to write transaction logic in C#, using the System.Data.SqlClient.SqlTransaction class that wraps calls to SQL Server statements or stored procedures. Another option is to utilize MTS (Microsoft Transaction Server) to support distributed transactions. When these approaches are used, writing T-SQL code to manage transactions typically can be avoided. Sometimes, however, it makes the most sense to manage transactions at the stored procedure level.
Sql Transaction Example
For example, you might choose to implement a process that requires numerous, process-intensive queries and data manipulation statements as a database stored procedure or a set of procedures. This tip will show you how to avoid transaction-related errors when nesting procedure calls in SQL Server.In its simplest form, here is a SQL Server stored procedure that manages a transaction:CREATE PROCEDURE dbo.simpleprocASBEGINBEGIN TRYBEGIN TRANSACTION;PRINT 'Executing simple proc.'
-Execute logic within transaction.COMMIT TRANSACTION;END TRYBEGIN CATCHROLLBACK TRANSACTION;RAISERROR ('Error in simple proc!' ,16,1)END CATCHEND.
A transaction is started. If things go well, changes are committed in the database. Otherwise, the changes are rolled back. This works fine in most situations.
That is, unless you have an environment in which you have other stored procedures that invoke this one and they too manage transactions. In such an environment, problems can arise because, though each 'BEGIN TRANSACTION' increments @@TRANCOUNT by one and each 'COMMIT TRANSACTION' decrements the count, a 'ROLLBACK TRANSACTION' rolls back all changes. That is, a rollback sets @@TRANCOUNT all the way back to zero. Attempting to execute a second rollback will generate an error with the message 'The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.' .This situation can occur if 'simpleproc' invokes a second procedure, 'anothersimpleproc', but 'anothersimpleproc' can also be called on its own. To account for the later scenario, 'anothersimpleproc' must manage its own transactions instead of letting a caller do it for him. Because, in reality, it is not feasible to always know which procedure will be the outermost procedure in the chain, a certain amount of flexibility is necessary.
Nested Stored Procedures
So, some logic must be written to deal with this.Next is an updated version of simple proc with some additional logic.CREATE PROCEDURE dbo.simpleprocASBEGIN-Holds info on whether or not this proc originated the-transaction.DECLARE @vtransstarted BITBEGIN TRYPRINT 'Executing simple proc.' SET @vtransstarted = 0IF @@TRANCOUNT = 0BEGINBEGIN TRANSACTION;SET @vtransstarted = 1ENDELSESET @vtransstarted = 0-Execute logic within transaction.IF @vtransstarted = 1BEGINSET @vtransstarted = 0COMMIT TRANSACTION;ENDEND TRYBEGIN CATCHIF @vtransstarted = 1BEGINSET @vtransstarted = 0ROLLBACK TRANSACTION;ENDRAISERROR ('Error in simple proc!' ,16,1)END CATCHENDPage 1 of 2.