Enforcing Procedural Business Logic with Stored Procedures

• Enforce procedural business logic using stored procedures.

Why use stored procedures at all? They do look like a lot of work, and there are many restrictions to keep in mind. However, there are several benefits to using stored procedures that far outweigh the costs.

• n- Tier Architecture . SQL Server can provide both the bottom tier (data storage) and middle tier (business logic) parts of a large-scale, client-server application. With judicious use of stored procedures and triggers, data integrity and business rules can be enforced.

• Speed . Stored procedures can be faster than equivalent "dynamic" batches because SQL Server can cache the query plan and reuse the query plan. So, the code is pre-parsed, pre-planned, and ready to run, eliminating some of the startup time for running a batch, especially a significantly large batch.

• Code Control . Typically, programmers write programs and database administrators spend most of their time either writing queries for the programmers or fixing broken queries from the programmers. If the queries are encapsulated into stored procedures, the queries can be changed by the database administrator easily, and the database administrator just has to keep the incoming parameters and outgoing data formatted the same, but can make other changes inside the stored procedure without changing the application code.

• Reducing Round Trips . Typically, applications that do not use stored procedures for handling data make repeated trips to the database to get little pieces of information, which are then correlated and usually applied to the database. This involves data making several trips across the network, and sometimes involves making and breaking several database connections. This is a lot less efficient than simply telling SQL Server how to handle your data and letting the stored procedure process the data without it leaving the server.

The stored procedures mentioned in this chapter are fairly simple. It is common in systems that use stored procedures extensively to have stored procedures that are hundreds or thousands of lines long, making them extremely complex. Having this type of logic in a language (T-SQL) that is designed to manipulate data in tables provides centralized management and efficiency improvements.

Stored procedures are used extensively to encapsulate business logic, especially by implementing transactions. Remember from Chapter 6 that one of the hallmarks of a good transaction processing system is that the transactions are isolated and do not require user input to proceed from beginning to end. Using stored procedures is an effective way to encapsulate an entire transaction into one line of execution: the stored procedure call. Using parameters to pass in data and resultsets to return output is a very common way to make sure that the data that comes in is validated and correct and that the correlations between the data being returned conform to established business rules.

For example, say that you have a system that needs to handle some business rules about sales representatives and their territories . Each sales rep has a territory made up of one or more zip codes, and is given credit for sales made within his or her zip codes only. You would build a table like this for the sales reps:

 CREATE TABLE SalesRep ( SalesRepID int IDENTITY(1,1), SalesRepName varchar(75), ) 

NOTE

Where's the Rest of the Structure? There are a lot of partial database designs from here out. These are not intended to be fully designed databases, and you should be thinking to yourself that the author should be storing first and last name separately. You're right. He should. But the names aren't an important part of the example; they're just present to flesh out the structure.

Then you'd have a related table to track zip codes for each sales rep, which would look like this:

 CREATE TABLE SalesRepZipCode ( SalesRepID int not null, ZipCode varchar(6) not null ) 

You need a table that contains customer records:

 CREATE TABLE Customer ( CustomerID int, CustomerName varchar(50), CustomerZip varchar(6) ) 

And finally, a table that contains sales records:

 CREATE TABLE Sales ( SalesID int identity(1,1), SalesRepID int, ProductID int, CustomerID int, QtySold int, Price float ) 

Jim the salesman sells 42 widgets to the customer with ID number 27. An application that logs this type of transaction would do something like this:

 create procedure LogSale @SalesRepID int, @ProductID int, @CustomerID int, @QtySold int, @Price float as begin declare @SoldInZipCode varchar(6) SELECT @SoldInZipCode = ZipCode         FROM Customer      WHERE CustomerID = @CustomerID if (SELECT count(*)              FROM SalesRepZipCode              WHERE SalesRepID = @SalesRepID                      and ZipCode = @ZipCode) = 0 begin RAISERROR("Unable to insert sale-Customer out of Sales Rep Territory", 16, 1) return 0 end else INSERT Sales VALUES (@SalesRepID, @ProductID, @CustomerID, @QtySold, @Price) return 1 end 

This is a fairly simple example. A more complete procedure would also check to make sure that the price the sales rep sold at was a valid price, that the quantity was valid, and so on. Using this technique, you can validate data before it enters your database, and ensure that it doesn't violate business rules.