Data Management

Test the SQL Server business rule chaining method

Chaining database stored procedures is an interesting and effective way to execute business logic in your database. Tim Chapman explains the concept of business rule chaining, and provides a very useful example for the implementation.

There is an age-old debate about where to store application business logic: the business logic layer in the application itself or at the database layer. Strict proponents of the application logic layer suggest that the sole purpose of the database is to only hold data for use by the application. Advocates of the database for business rule storage argue that business rules are best stored in the database because that is where the data is stored and rules are easier to run there. It is my belief that there is not one "best place" for storing logic for your application—it really depends on the business problem that you are trying to solve.

Chaining database stored procedures

If you prefer to store all or some of your business logic in the database, it is beneficial to be aware of a technique in SQL Server that I refer to as business rule chaining. The basic idea is that you can run a series of stored procedures in the database based upon metadata stored in a database table for different processes as you need them. The benefit is that the rules are stored in procedures in the database, and because running the stored procedures is based upon values in a table, you can change the sequence in which the procedures are executed, and turn a business rule on or off very easily. Let's take a look at an example to make the concept clearer.

A business rule chaining example

To execute the business rules in the database in the fashion that I want, I must define the metadata. This information will be in the form of a database table, which I will use to store: the name of the stored procedure, the sequence in which I need the business rule to run, the type of business procedure that I am running, and whether the business rule is active. Listing A contains a script to create the table.

Weekly SQL tips in your inbox
TechRepublic's free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system.
Automatically sign up today!

In Listing B, I load data into the BusinessLogic table. This is the data that I will use later in the example to process the business rules. The RunSequence is the actual ordering in which the stored procedure will execute (the procedure is stored in the LogicProcedure field). The table also includes an indicator as to if the business rule is active. Storing this data allows me to change the running sequence of rules or turn them on and off as necessary without changing any actual code. It is also very easy to add rules to the business logic system because all that is necessary is to add the procedure to the database and then add any necessary data to the metadata table.

In Listing C, I create the business rule procedures. (The examples contain very simple procedures; however, in real-world scenarios, these can be as complex as needed.) All the procedures contain the same input parameters; this is a minor restriction to the business rule chaining scenario. I discuss this in a bit more detail later in the article, and offer suggestions about how to work around it.

Now comes the code used for processing the business rules. In Listing D, I use a cursor to iterate through the records in the table that holds the metadata. While it is possible to use a different loop structure to accomplish the same logic, it is easier to use a cursor. Regardless of how it is done, it is necessary that some type of iteration is used to cycle through and execute the required business procedures. Running this code will execute each of the four stored procedures defined previously in the article.

There are two main points of interest in Listing D. First is the select statement used to retrieve the records from the table that holds the information for processing the business rules. From this simply query, I can return the rows from the BusinessLogic table for any type of business process. I can also ensure that the rules are active and returned in the order in which they need to execute.

Second is the manner in which the business rules are executed. As the cursor iterates, it retrieves the the name of the stored procedure to be executed from the BusinessLogic table and stores it in a local variable. The EXECUTE command allows the user to execute this stored procedure even though the name of the stored procedure is stored within a variable. Calling the stored procedure in this fashion still allows me to pass in necessary parameters to the business procedure.

This leads me back to my previous point regarding the business procedures having the same number of input parameters. I am able to run the business procedures in a somewhat dynamic fashion depending on what is stored in the BusinessLogic table when the procedures are run. However, I currently don't have a method of dynamically passing parameters to the business procedures.

The simple solution is to ensure that all of the business procedures accept the same number of parameters, whether these parameters are used or not. This technique ensures that we always provide the necessary parameters to our business procedures. There are other ways to accomplish the passing of these parameters only as needed, but those methods are beyond the scope of this article.

Recap

If your application(s) store any or all of its business logic in the database, then it is possible that the application is a candidate for what I refer to as business rule chaining. This method allows for the sequential running of stored procedures in the database, and lets you to turn these business rules on and off as necessary. Some potential drawbacks to using this method include data security (the data for executing the business procedures are stored in a table), and the non-dynamic nature of passing parameters to the business logic procedures. If you feel that the benefits outweigh the drawbacks for your business problem, I invite you to try this solution.

Tim Chapman is a SQL Server database administrator who works for a bank in Louisville, KY, and has more than 7 years of IT experience. He is also Microsoft certified in SQL Server 2000 and SQL Server 2005. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.

About

Tim Chapman is a SQL Server MVP, a database architect, and an administrator who works as an independent consultant in Raleigh, NC, and has more than nine years of IT experience.

4 comments
rits4friends
rits4friends

Hi Tim, first of all, congratulations for being MVP, You should write it here in your signature, this is just a friendly advice. Ritesh Shah

tnickelsen
tnickelsen

I just came across this post and I have to say it is a familiar approach to a complex problem. I have been designing databases for about 10 years and understand how tempting it is to try to use iteration to process data in a query, especially if they are primarily C# or C++ programmers. Unfortunately, SQL Server is an object oriented DBMS and is not designed to handle iteration with even basic efficiency. You see SQL Server is designed to work with batches. The Query Optimizer is all about batches. Unfortunately iteration defeats this very excellent way to handle data and reverts back to the basic one record at a time (list processing) approach. Though common it unfortunate and naiive.

maryRRR
maryRRR

...since the app I am building in .NET will be internal AND I am a remote consultant AND I like to empower my users...whether this might be a good approach to set up for the client's named Admin to run some scripts once I'm gone. For instance, they will need to update tables (SQL Express 2k5) that I could possibly schedule(?) on some sort of regular basis. But they will also need to change/add Roles for existing/new users of the app. Perhaps even refresh on-demand other data we're pulling in from their 3rd party software tables. If I'm reading your article correctly, this might be a good way to bundle up the procedures and be sure they are run in the proper sequence. Am I on-track?? Thanks!

chapman.tim
chapman.tim

Sorry for the delay at getting back to you. Yes, you may be able to use this for what you are trying to accomplish. The idea I was trying to get across is that you can store the procedure names in tables, and look through the records dynamically and call the procedures as you need to.