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.
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.
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 firstname.lastname@example.org.
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.