Test SQL Server Business Rule link method

There is an age-old controversy about where to store the application business logic: In the business logic layer of the application itself or in the database tier. The absolute proponents of the application logic layer suggest that the only purpose of the database is to save the data for use by the application. Advocates of using databases to store business rules insist that business rules are best stored in the database because the data is also stored there, where rules are easier to run. In my opinion, there is no "best place" for the logic of storing applications-it really depends on the business problem you are working on.

Linked Database stored procedures

If you prefer to store all or part of your business logic in a database, it's good to know a technology in SQL Server that I call a 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 stored in a database table at the time you need to have different processes. The advantage of this is that the rules are stored in the database's programs, and because stored procedures are run based on values in a table, you can change the order in which the programs are executed, and you can easily open or terminate the business rules. Let's take a look at an example, so the concept will be clearer.

Business Rule Link Instance

To execute business rules in the database the way I want to do it, you must define the metadata. The following information will be saved in the form of a database table: the name of the stored procedure, the order in which the business rules are run, the type of business program that is running, and whether the business rules are active. The script to create the table is included in List A.

In list B, I loaded the data in the Businesslogic table. These are the data that I will use to deal with business rules later. Runsequence is the actual order in which the stored procedure is executed (the procedure is stored in the Logicprocedure field). The table also contains an indicator to indicate whether the business rules are active. Storing this data allows me to change the order in which the rules are run, or to open or terminate the rules when needed without making changes to the code. Adding a rule to a business logic system is also easy, because all you need to do is add the program to the database and then add the data you need in the metadata table.

In list C, I created the Business Rules program (the program included in the example is very simple; in reality, however, they can be complex if needed). All programs include the same input parameters; This is a small limitation of the business Rule link.

The next step is the code that handles the business rules. In List D, I iterate through the table with a pointer, and the records in the table hold the metadata. When a different loop structure can be used to complete the same logic, it is simpler to use a pointer. Regardless of how it is done, you need to loop through some type of iteration and execute the required business program. Running this code will execute the four stored procedures defined earlier in each article.

In List D, there are two main areas of attention. The first is a SELECT statement that retrieves records from a table, and the records that are retrieved contain information that handles business rules. From this simple query, I can return rows from the Businesslogic table for any type of business process. I can also guarantee that the rules are active and return in the order in which they need to be performed.

The second is the way the business rules are executed. When the pointer is iterated, it retrieves the name of the stored procedure that will be executed from the Businesslogic table and stores it in a logical variable. The Execute command allows a user to execute a stored procedure, even if the name of the stored procedure is stored in a variable. In this way, invoking the stored procedure also enables me to enter the required parameters into the stored procedure.

This brings me back to the point where the business program has the same number of input parameters as before. I can run business programs in a fairly dynamic way, depending on what is stored in the Businesslogic table when the program is running. But now I don't have a way to dynamically enter parameters into a business program.

A simple solution is to ensure that all business programs accept the same number of parameters, regardless of whether they are used or not. This technology ensures that we always provide the required parameters for the business process. There are other ways to implement the input of these required parameters, but that is not what this article is about.

Briefly restate

If your application stores any or all of its business logic in the database, it is possible that it is a candidate I call a business rule link. This approach allows stored procedures to run sequentially in the database, and allows you to open or terminate these business rules when needed. Some of the potential pitfalls of using this approach include data security (the data that executes the business program is stored in a table), and the non dynamic input parameters to the business logic program. I encourage you to try this approach if you find this approach more beneficial than the downside to your business problems.

Tim Chapman is a SQL Server database administrator at a bank in Louisville, Kentucky State, who has over 7 years of industry experience. He also passed the certification of Microsoft SQL Server 2000 and SQL Server 2005.