Test the SQL server business rule Link Method

There is an old debate about where to store applications Program Business logic: whether it is in the application's business logic layer or in the database layer. The absolute supporter of the application logic layer proposed that the only purpose of the database is to save data for use by the application. Those who advocate the use of databases to store business rules insist that business rules are best stored in databases because data is also stored there and rules are easier to run. In my opinion, there is no "best place" for the logic of storing an application-it really depends on the business problem you are solving.

Linked database Stored Procedures

If you prefer to store all or part of the business logic in the database, it is advantageous to know a technology in SQL Server called Business Rule link. The basic idea is that you can run a series of stored procedures in the database, which is based on the metadata of different processes stored in a database table when you need them. The advantage of this is that the rules are stored in the database program, and because the operation of the stored procedure is based on the values in a table, you can change the execution sequence of the program, it can also easily enable or terminate business rules. Let's look at an example to make the concept clearer.

Business Rule linked instance

To execute business rules in the database in the way I want to use it, metadata must be defined. The following information will be saved as a database table: the name of the stored procedure, the order in which the business rules run, the type of the running business program, and whether the business rules are active. List A contains the script for creating a table.

In List B, I load data in the businesslogic table. This data will be used to process business rules later. Runsequence is the actual sequence of execution of stored procedures (the procedures are stored in the logicprocedure field ). The table also contains an indicator to indicate whether the business rule is active. Storing this data allows me to change the rule running sequence, or enable or terminate the rule as needed without havingCodeMake changes. It is easy to add rules to the business logic system, because you need to add programs to the database and then add the required data to the metadata table.

In list C, I created a business rule program (the programs included in the example are very simple; however, they can be very complicated if needed in reality ). All programs include the same input parameters; this is a small limitation of Business Rule links.
The following is the code for processing business rules. In List D, I use a pointer to iterate in the table. The records in this table store metadata. When we can use a different loop structure to complete the same logic, it is easier to use pointers. No matter how it is done, you need to use some type of iteration loop and the business program required for execution. Running this code will execute everyArticleThe four stored procedures defined above.

In List D, there are two main points of attention. The first is the SELECT statement used to retrieve records from a table. The retrieved records contain information for processing business rules. From this simple query, I can return rows from the businesslogic table for any type of business processing. I can also ensure that the rules are active and are returned in the order they need to be executed.

The second approach is to execute business rules. During pointer iteration, It retrieves the name of the stored procedure to be executed from the businesslogic table and stores it in a logical variable. The execute command allows you to execute a stored procedure even if the name of the stored procedure is stored in a variable. In this way, calling a stored procedure also enables me to input the required parameters to the stored procedure.

This enables me to return to the previous point that the business program has the same number of input parameters. I can run a business program in a rather dynamic way, depending on what is stored in the businesslogic table when the program is running. However, there is no way for me to dynamically input parameters to the business program.

A simple solution is to ensure that all business programs accept the same number of parameters, no matter they are used. This technology ensures that we always provide the required parameters for the business program. There are other ways to implement the input of these required parameters, but those are not discussed in this article.

Summary

If your application stores any or all of its business logic in the database, it may be a candidate for what I call a business rule link. This method allows the stored procedure to run in sequence in the database, and allows you to enable or terminate these business rules as needed. Some potential flaws in this approach include data security (data for executing business programs is stored in a table) and non-dynamic input of parameters to Business Logic Programs. If you think this method is more advantageous than a disadvantage for your business problems, I encourage you to try this method.