Stored Procedures  «Prev  Next»

Lesson 3Enforcing business rules with stored procedures
ObjectiveDescribe 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:
  1. 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.
  2. 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.

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

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:
  1. 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.
  2. 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.
  3. 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.