Lesson 3	Enforcing business rules with stored procedures
Objective	Describe how stored procedures can aide in enforcing business rules.

Enforcing Business Rules using Stored Procedures

To enforce business rules using stored procedures, you first need to have a handle on exactly what the rules for the business are. Once you know what the rules are, you can divide them into logical units of work. These units of work can be one or more stored procedures, depending on how often the rule needs to be checked or enforced.
Consider the following rules:

When a new employee is hired, the employee’s contact information is entered in to the Employees table, and the department manager is notified by email.
When an employee is transferred, his or her new department number is entered into the CompanyOrg table, and the department manager is notified by email.

Stored Procedures can aide in enforcing Business Rules

Stored procedures in SQL Server 2019 play a pivotal role in enforcing business rules within the database environment, primarily due to their ability to encapsulate complex logic and operational controls. Here's how they contribute to this aspect:

Centralization of Business Logic: Stored procedures allow for the centralization of business logic within the database layer. By encapsulating the rules and conditions that define business processes, they ensure consistent application of these rules across various applications and services that access the database. This central repository of business logic prevents discrepancies and errors that can occur when business rules are implemented across multiple client applications.
Consistency and Integrity of Data: Through stored procedures, businesses can enforce data validation, integrity checks, and conditional operations based on specific criteria. For instance, a stored procedure can be designed to check the validity of input data against business rules before inserting or updating records. This approach ensures that only data conforming to predefined business rules is allowed into the database, thereby maintaining data integrity and consistency.
Transactional Control: Stored procedures can be used to implement complex transactional logic that adheres to business requirements. By controlling transactions within the procedures, businesses can ensure that a series of operations either complete successfully as a unit or get rolled back entirely in case of an error. This transactional integrity is crucial for maintaining consistent and reliable data states within the database, especially in scenarios where multiple related operations need to be executed in sequence.
Customized Access and Security: The ability to embed business rules within stored procedures also enhances security. Access to data can be controlled by exposing only specific stored procedures to end users or applications, rather than providing direct access to tables. This method allows for the implementation of business rules that govern data access, ensuring that users can only execute predefined operations that adhere to business policies.
Automation and Efficiency: Stored procedures can automate complex or repetitive tasks that adhere to business rules. For example, a stored procedure could be scheduled to run at specific intervals to perform maintenance tasks like data archiving or cleanup based on business-defined criteria. This automation not only saves time but also ensures that such operations are carried out consistently and without manual intervention.

In conclusion, stored procedures in SQL Server 2019 are a powerful tool for enforcing business rules. They provide a secure, centralized, and efficient way to manage business logic, ensuring data integrity, consistency, and adherence to organizational policies and procedures.

The following series of images shows different ways of constructing stored procedures to address the business rules above:

1) You could construct one stored procedure for new hires that inserts contact information into the Employee table and emails the department manager

2) and a second stored procedure that enters a transferred employee's new department number in the CompanyOrg table, and e-mails the department manager

3) However, the two stored procedures have a common task: each identifies the department head and sends that person an e-mail.

4) A better solution would be to create a third stored procedure that identifies and sends e-mails to an employee’s department manager, based on that employee’s employee ID.

5) The third stored procedure is called from either of the first two procedures. You have now encapsulated the common element of both business rules into a third procedure, and minimized the amount of code required to support your business rules.

Stored procedures and Client-Server Architecture

Different client/server installations will vary according to the layers, or tiers, that make up that environment. The tiers can be a conceptual separation, rather than a physical one. Most client/server installations can be categorized into one of three architectures: two tier, three tier, and n-tier:

A two-tier architecture consists of a client application (on the user's computer) and a server (SQL Server). In this configuration, stored procedures reside on the second tier because the processing takes place on the server.
A three-tier configuration has an additional middle tier that holds all the business rule logic. This way, if the business rules change, you need to change only the middle tier. In a three-tier installation, stored procedures can be in the middle tier.
An n-tier approach simply means that you have multiple middle tiers. In an n-tier installation, stored procedures can exist in any of the middle tiers.

If the installation that you are working on is a three- or n-tier installation, you should design stored procedures that enforce business rules to exist in the middle tier(s). This will help you isolate changes that you will need to make to your stored procedures when business rules change. In the next lesson, the creation of stored procedures will be practiced.