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.
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. |