In order to reuse programmatical object in SQL server (procedures, functions), a SQL developer might need to use nested stored procedures to be able to reuse codes on different level of stored procedures.
Goal
We are going to show a simple case of nested stored procedures with an orchestration of sql transaction in order to:- Set a try catch solution in order to propose a personalized “catch” response- Set up a simple but efficient management of Sql transaction within a stored procedure
Challenges
Depending whether you execute the stored procedure on its own or part of an ensemble of task, your sql transaction might be begun, committed/rollbacked inside the stored procedure or not. It is then very important to manage such flexibility for reusability of the stored procedure. Additionally, it is important to have an elegant solution to give an appropriate and personalized answer to a failure.
Templating the stored procedure
I have designed thousands of stored procedures in the past and I came up with a template that seems to work well on most of the cases of failures and successes of stored procedure execution. This is not THE solution. You can draw your own solution. This one suits me well.
CREATE PROCEDURE ...
AS
-- related to context of transaction isolation, session settings...
-- declaring a variable to keep transaction count and error message to throw in the catch section
DECLARE @Trancount INT = @@TRANCOUNT,
@ErrMsg VARCHAR(512) = ''
-- if transaction has not been set, begin the transaction (I am over protective with the check on @@trancount)
IF @Trancount = 0 AND @@trancount = 0
BEGIN TRAN
-- Start the try section
BEGIN TRY
-- execute what needs to be executed...
-- check again the transaction count to make sure there is a transaction open (again, over protective),
-- if one, then commit unless initiated outside the sproc
IF @Trancount = 0 AND @@trancount > 0
COMMIT TRAN
END TRY
BEGIN CATCH
-- setting variable within catch before rollback is fine as the variable values are kept
SET @ErrMsg = ERROR_MESSAGE()
-- same as commit section, if the transaction was initiated within sproc, do rollback
IF @Trancount = 0 AND @@trancount > 0
ROLLBACK TRAN
-- execute a personal response...
-- example
SELECT @ErrMsg AS ErrorMsg
-- you can also raise the error so that it is picked up by application or the parent sproc
RAISERROR(@ErrMsg, 16, 1)
END CATCH
Note that the main idea of the template is to:
- Make sure the transaction is handled at the level it has been initiated. If within the stored procedure, @Trancount is then equal to zero and therefore is managed within the stored procedure (commit/rollback). If outside, the @Trancount > 0 and then is not managed at all (an alternative is to use SAVEPOINT in this context to mark the transaction point in the transaction log)
- Make sure the stored procedure can be used on its own or nested and the transaction will be handled gracefully at the level it needs to be handled.
- Make sure that the catch section does give the appropriate answer whether the stored procedure is nested or not. Raising error allows you to handle it on the other stored procedure as an error and able to handle it appropriately within the main catch section or a nested cache section. Other way is to use a return code so that a specific return code can trigger a specific action.
Example
Here is an example of nested stored procedure handling such sql transaction gracefully. Additionally I tossed a json input parameter to pass on rich record set to the stored procedure. Some of the developers might be interested to see how json is parsed.
1. Creating the data model on database Nested Transaction
On the schema Orders, we create the header of the order, the detail of the order and the event related to the order.
USE [NestedTransaction]
GO
/****** Object: Schema [Orders] Script Date: 28/01/2020 14:32:58 ******/
CREATE SCHEMA [Orders]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Orders].[Detail]
(
[OrderId] [int] NOT NULL,
[OrderRow] [tinyint] NOT NULL,
[ProductId] [smallint] NOT NULL,
[Quantity] [smallint] NOT NULL,
[OrderDetailStatus] [smallint] NOT NULL,
CONSTRAINT [PK_OrdersDetail] PRIMARY KEY CLUSTERED
(
[OrderId] ASC,
[OrderRow] ASC
)
WITH
(
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Orders].[EventLog]
(
[logid] [int] IDENTITY(1,1) NOT NULL,
[WebId] [int] NULL,
[OrderLogDate] [datetime] NULL,
[jsonlogged] [nvarchar](max) NULL,
NULL,
PRIMARY KEY CLUSTERED
(
[logid] ASC
)
WITH
(
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [Orders].[Header] Script Date: 28/01/2020 14:32:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Orders].[Header]
(
[OrderId] [int] IDENTITY(1,1) NOT NULL,
NOT NULL,
[CustomerId] [int] NOT NULL,
[OrderDate] [datetime] NOT NULL,
[OrderStatus] [smallint] NOT NULL,
CONSTRAINT [PK_OrdersHeader] PRIMARY KEY CLUSTERED
(
[OrderId] ASC
)
WITH
(
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [Orders].[Detail]
ADD DEFAULT ((1)) FOR [OrderDetailStatus]
GO
ALTER TABLE [Orders].[Header]
ADD DEFAULT (GETDATE()) FOR [OrderDate]
GO
ALTER TABLE [Orders].[Header]
ADD DEFAULT ((1)) FOR [OrderStatus]
GO
ALTER TABLE [Orders].[Detail] WITH CHECK
ADD CONSTRAINT [FK_OrdersDetail_OrdersHeader_OrderId]
FOREIGN KEY([OrderId])
REFERENCES [Orders].[Header] ([OrderId])
GO
ALTER TABLE [Orders].[Detail]
CHECK CONSTRAINT [FK_OrdersDetail_OrdersHeader_OrderId]
GO
2. Creating the stored procedures
The following code creates two stored procedures to create an order and an event. Assume that we are using an Ecommerce website to handle it. I simplified at the maximum for the purpose of training.
USE [NestedTransaction]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
DECLARE @OrderJSon nvarchar(max)
SET @OrderJSon = '{
"ord": [
{
"orderref": "1AXFRD",
"customerid": "1",
"product_list": [
{"orderRow":"1","productId":"234","quantity":"2"},
{"orderRow":"2","productId":"125","quantity":"6"}
]
}
]
}'
EXEC Orders.AddEventLog @WebId = 1, @jsonLogged = @OrderJSon, @ErrorTxt = NULL
*/
CREATE PROCEDURE [Orders].[AddEventLog]
@WebId int,
@jsonLogged nvarchar(max),
@ErrorTxt nvarchar(1024)
AS
SET NOCOUNT ON;
DECLARE @Trancount int = @@TRANCOUNT,
@ErrMsg varchar(512) = ''
IF @Trancount = 0 AND @@trancount = 0
BEGIN TRAN
BEGIN TRY
INSERT INTO [Orders].[EventLog] ([WebId], [OrderLogDate], [jsonlogged], [ErrorTxt])
VALUES (@WebId, GETDATE(), @jsonLogged, @ErrorTxt)
IF @Trancount = 0 AND @@trancount > 0
COMMIT TRAN
END TRY
BEGIN CATCH
SET @ErrMsg = ERROR_MESSAGE()
IF @Trancount = 0 AND @@trancount > 0
ROLLBACK TRAN
SELECT @ErrMsg AS ErrorMsg
END CATCH
GO
/****** Object: StoredProcedure [Orders].[AddOrder] Script Date: 28/01/2020 14:32:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- 3. CREATE PROCEDURE TO ADD AN ORDER -- case of simple SQL transaction
/*
DECLARE @OrderJSon nvarchar(max)
SET @OrderJSon = '{
"ord": [
{
"orderref": "1AXFRD",
"customerid": "1",
"product_list": [
{"orderRow":"1","productId":"234","quantity":"2"},
{"orderRow":"2","productId":"125","quantity":"6"}
]
}
]
}'
EXEC Orders.AddOrder @OrderJSon = @OrderJSon
*/
CREATE PROCEDURE [Orders].[AddOrder]
@OrderJSon nvarchar(max)
AS
SET NOCOUNT ON;
DECLARE @Trancount int = @@TRANCOUNT,
@ErrMsg varchar(512) = ''
IF @Trancount = 0 AND @@trancount = 0
BEGIN TRAN
BEGIN TRY
DECLARE @Orders TABLE
(
OrderId int null,
OrderRef varchar(32),
CustomerId int,
OrderRow tinyint,
ProductId smallint,
Quantity smallint
)
INSERT INTO @Orders (OrderRef, CustomerId, OrderRow, ProductId, Quantity)
SELECT
ord.orderref AS OrderRef,
ord.customerid,
product_list.orderrow,
product_list.productid,
product_list.quantity
FROM OPENJSON (@OrderJSon)
WITH (ord nvarchar(max) AS json) AS rt
CROSS APPLY OPENJSON (rt.ord)
WITH (orderref varchar(32), customerid int, product_list nvarchar(max) AS json) AS ord
CROSS APPLY OPENJSON (ord.product_list)
WITH (orderRow tinyint, productId smallint, quantity smallint) AS product_list
DECLARE @OutPutHeader TABLE (OrderId int, OrderRef varchar(32))
INSERT INTO Orders.Header (OrderRef, CustomerId)
OUTPUT Inserted.OrderId, Inserted.OrderRef INTO @OutputHeader (OrderId, OrderRef)
SELECT DISTINCT OrderRef, CustomerId
FROM @Orders ord
UPDATE PUB
SET OrderId = SUB.OrderId
FROM @Orders PUB
JOIN @OutPutHeader SUB ON PUB.OrderRef = SUB.OrderRef
INSERT INTO Orders.Detail (OrderId, OrderRow, ProductId, Quantity)
SELECT OrderId, OrderRow, ProductId, Quantity
FROM @Orders
IF @Trancount = 0 AND @@trancount > 0
COMMIT TRAN
END TRY
BEGIN CATCH
SET @ErrMsg = ERROR_MESSAGE()
IF @Trancount = 0 AND @@trancount > 0
ROLLBACK TRAN
SELECT @ErrMsg AS ErrorMsg
END CATCH
GO
/****** Object: StoredProcedure [Orders].[AddOrderAndEventLog] Script Date: 28/01/2020 14:32:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
DECLARE @OrderJSon nvarchar(max)
SET @OrderJSon = '{
"ord": [
{
"orderref": "1AXFRD",
"customerid": "1",
"product_list": [
{"orderRow":"1","productId":"234","quantity":"2"},
{"orderRow":"2","productId":"125","quantity":"6"}
]
}
]
}'
EXEC Orders.AddOrderAndEventLog @WebId = 1, @OrderJson = @OrderJSon
*/
CREATE PROCEDURE [Orders].[AddOrderAndEventLog]
@OrderJson nvarchar(max),
@WebId int
AS
SET NOCOUNT ON;
DECLARE @Trancount int = @@TRANCOUNT,
@ErrMsg varchar(512) = ''
IF @Trancount = 0 AND @@trancount = 0
BEGIN TRAN
BEGIN TRY
EXEC Orders.AddOrder @OrderJSon = @OrderJSon
EXEC Orders.AddEventLog @WebId = 1, @jsonLogged = @OrderJSon, @ErrorTxt = NULL
IF @Trancount = 0 AND @@trancount > 0
COMMIT TRAN
END TRY
BEGIN CATCH
SET @ErrMsg = ERROR_MESSAGE()
IF @Trancount = 0 AND @@trancount > 0
ROLLBACK TRAN
EXEC Orders.AddEventLog @WebId = 1, @jsonLogged = @OrderJSon, @ErrorTxt = @ErrMsg
SELECT @ErrMsg AS ErrorMsg
END CATCH
GO
3. Executing the stored procedures
I always put an example on the header of the stored procedures. I think it is a good practice for reusability of the code and testing. I also usually put
Executing:
Does insert the orders (insert onto 2 tables) and then insert a log. But it does it gracefully by rollbacking the insert on the two tables in case of an error and then insert the error into a log.
As you can above, if we have a failure in the orders’ creation, it does go directly on the catch section of the parent stored procedure, rollbacking the inserts and storing the event log. If we were to use the nested stored procedure to insert the order on its own, it would also rollback the insert and throw a personalized error.
DECLARE @OrderJSon nvarchar(max)
SET @OrderJSon = '{
"ord": [
{
"orderref": "1AXFRD",
"customerid": "1",
"product_list": [
{"orderRow":"1","productId":"234","quantity":"2"},
{"orderRow":"2","productId":"125","quantity":"6"}
]
}
]
}'
EXEC Orders.AddOrderAndEventLog
@WebId = 1,
@OrderJson = @OrderJSon;