(This blog post was first published on 25-nov-2013 on my former blog)
The challenges
Working with SQL Server and stored procedures for several years, I have faced several challenges like:
- reuse of stored procedures: make sure stored procedures are small components that you can use for different purposes.
- maintainability of stored procedures: make sure stored procedures do not become large ‘procedural written’, otherwise they can become hard to maintain due to this.
- generating code versus manual maintenance: how to merge generated and manual SQL code easily.
- where to implement error handling and the automic unit using a transaction: how to ensure these without harming the needs above.
And I have found a way to deal with this: The CONTROLLER - TASK design pattern for stored procedures. This pattern solves my challenges into a working solution!
1) Reuse of stored procedures
Sometimes you want to reuse a particular INSERT, UPDATE or DELETE statement. When it is in a stored procedure you can simply call this stored procedure! But what if this stored procedure also contains other code, uses its own transaction or implements its own error handling? Then this might be not that simple!
The CONTROLLER-TASK design pattern for stored procedures makes reuse of SQL code very easy.
2) Maintainability of stored procedures
A best practice in C# programming (or any type of programming really) that has become even more popular over the last years, is to create short methods containing only a small amount of code, and having a “main” method to call all the short methods.
Advantages: the main method is very readable (when method names are self-explaining), and code stays really maintainable. Also, short methods can be unittested easier.
Why not do the same in SQL code then?
Why not have an inventory of short stored procedures, either generated or manually made, that do only one task, and shop around to glue together what you need in a “main” stored procedure?
When working like this in a team:
- tasks can be divided easier when stored procedures are short
- stored procedures are easier to understand for new developers when they are short
The CONTROLLER-TASK design pattern for stored procedures makes your SQL code better maintainable.
3) Generating code versus manual maintenance
How convenient would it be to generate generic stored procedures, for instance UPDATE stored procedures for each table, a LOAD stored procedure for each hub in a datavault datawarehouse, and so on? Generating is not extremely more difficult than creating normal stored procedures. Just use tables like sys.objecs, sys.columns, and so on, and a lot is possible.
Imagine these stored procedures populate your inventory of “tasks”, ready to use and glue together in a “main” stored procedure?
The CONTROLLER-TASK design pattern makes combining both manually made and generated SQL code to a working solution extremely easy.
4) Where to implement the error handling and automic unit using a transaction?
Is a stored procedure still reusable, if it is running in its own transaction? Maybe, if you use nested transactions. But there are drawnbacks and risks. Code can become messy when not implemented in the same way in different stored procedures.
Is a stored procedure still reusable, if it implements its own error handling? Maybe, but you have to be careful. If it ‘swallows’ the error, the stored procedure that calls it will not notice it and will continue after the error. This is almost never what you want. Besides that, implementing error handling in every stored procedure, makes your code base consist of many more lines.
So .. why not divide the “core” SQL code to run, and the “housekeeping” (automic unit, error handling)? No more confusion, it is clear where things are done, and code is easier reusable and maintainable.
The CONTROLLER-TASK design pattern divides core functionality and housekeeping code, therewith keeping your code cleaner and again .. better maintainable!
My solution, easy to implement
My answer to the challenges above is the CONTROLLER-TASK design pattern for stored procedures. As a picture says more than a thousand words, take a close look at the one below.
Getting started
Allright, if you see the value of it, how can you implement the CONTROLLER-TASK design pattern?
In its basic form, you’ll need only a few things: a naming convention and two stored procedure templates.
I will tell you how I did this, so you can use or modify it in the way you prefer.
Choosing a naming convention
As explained, you have three types of stored procedures:
- Task stored procedures (manually made)
- Task stored procedures (generated)
- Controller stored procedures
I just prefix my stored procedures with the typename as follows:
- TASK_<BaseStoredProcName> for stored procedures that are manually made
- GTSK_<BaseStoredProcName> for stored procedures that are generated
- CTLR_<BaseStoredProcName> for controller stored procedures
Of course you can invent your own different naming convention, as long as you use it consequently and document it for new developers working on your project.
Using two templates
Here are my templates, you can modify for your own needs
Prerequisites
TASK stored procedures
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N’sp.TASK_ReplaceWithStoredProcBaseName’) AND type in (N’P’, N’PC’))
DROP PROCEDURE sp.TASK_ReplaceWithStoredProcBaseName
GO
/* PRESS CTRL-SHIFT-M in SSMS to fill parameters
==========================================================================================
Author :
Create date :
Parameters:
- Input : none
- Output : none
Returnvalue : 0 if executed successfully.
Description :
==========================================================================================
*/
CREATE PROCEDURE sp. TASK_ReplaceWithStoredProcBaseName
AS
BEGIN
---------------------------------------------------------------------------------------------------
-- DECLARATIONS
---------------------------------------------------------------------------------------------------
--N/A
---------------------------------------------------------------------------------------------------
-- INITIALIZATION
---------------------------------------------------------------------------------------------------
--N/A
---------------------------------------------------------------------------------------------------
-- MAIN
---------------------------------------------------------------------------------------------------
SELECT 1 AS [Replace with your own code]
END
GO
Controller stored procedures
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N’sp.CTLR_ReplaceWithStoredProcBaseName’) AND type in (N’P’, N’PC’))
DROP PROCEDURE sp.CTLR_ReplaceWithStoredProcBaseName
GO
/* PRESS CTRL-SHIFT-M in SSMS to fill parameters
==========================================================================================
Author :
Create date :
Parameters:
- Input : none
- Output : none
Returnvalue : 0 if executed successfully.
Description :
==========================================================================================
*/
CREATE PROCEDURE sp.CTLR_ReplaceWithStoredProcBaseName
AS
BEGIN
---------------------------------------------------------------------------------------------------
-- TEMPLATE CODE: CONFIGURATION OF CONTROLLER, BEGIN TRANSACTION, INFO LOGGING
---------------------------------------------------------------------------------------------------
BEGIN TRY
SET XACT_ABORT ON
BEGIN TRANSACTION
---------------------------------------------------------------------------------------------------
-- (NON-TEMPLATE) DECLARATIONS
---------------------------------------------------------------------------------------------------
-- N/A
---------------------------------------------------------------------------------------------------
-- EXECUTE TASK STORED PROCEDURES
---------------------------------------------------------------------------------------------------
-- Example call:
EXECUTE sp.TASK_ReplaceWithStoredProcBaseName
--
---------------------------------------------------------------------------------------------------
-- TEMPLATE CODE: COMMIT TRANSACTION
---------------------------------------------------------------------------------------------------
COMMIT TRANSACTION
END TRY
BEGIN CATCH
---------------------------------------------------------------------------------------------------
-- TEMPLATE CODE: CATCH BLOCK WITH ROLLBACK AND THROW
---------------------------------------------------------------------------------------------------
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
END
-- Options here:
--\
---> 1) Log the error (optional)
--/
-- Not part of the BASIC template
--\
---> 2) Rethrow the error (optional)
--/
-- Syntax for SQL Server 2012:
-- ; THROW
-- Syntax for SQL Server 2008 and earlier:
DECLARE @ORIGINAL_ERROR NVARCHAR(MAX)
, @ERROR_SEVERITY INT = ERROR_SEVERITY()
, @ERROR_STATE INT = ERROR_STATE()
SELECT @ORIGINAL_ERROR = ” + ERROR_MESSAGE()
+ ‘ (ERROR_NUMBER=’ + CAST(ERROR_NUMBER() AS NVARCHAR)
+ ‘, ERROR_LINE=’ + CAST(ERROR_LINE() AS NVARCHAR)
+ ‘, ERROR_PROCEDURE=’ + ERROR_PROCEDURE() + ‘)’
RAISERROR(@ORIGINAL_ERROR, @ERROR_SEVERITY, @ERROR_STATE)
END CATCH
---------------------------------------------------------------------------------------------------
-- RETURN THE RESULT WHEN EXECUTED SUCCESFULLY
---------------------------------------------------------------------------------------------------
RETURN 0;
END
GO
Summary
In this blogpost I have explained to you the advantages of the CONTROLLER - TASK design pattern for stored procedures, which are:
- makes reuse of SQL code very easy.
- makes your SQL code better maintainable.
- makes combining both manually made and generated SQL code to a working solution extremely easy.
- divides core functionality and housekeeping code, therewith keeping your code cleaner and again .. better maintainable!
After that you were shown how to get started with a basic implementation of this design pattern.
Thank you for reading my blogpost and I hope you enjoyed it and found it useful.
