Filtering Data Using Stored Procedures

• Filter data using stored procedures.

Stored procedures can also be used to filter data. This is critical in web-based applications, where you tend to present a lot more data than you receive. For example, say that you're designing a web site for an auto insurance company. You want to provide a page that shows customers a list of claims centers in their city that they are eligible to use based on the insurance plan they have. So you need a table of customers, like this:

 CREATE TABLE Customer ( CustomerID int IDENTITY(1,1), CustomerName varchar(100), PlanID int ) 

And you also need to have a table of claim centers:

 CREATE TABLE ClaimCenter( ClaimCenterID int IDENTITY (1,1), ClaimCenterAddress varchar(100), ClaimCenterCity varchar(100), ClaimCenterState varchar(2) ) 

Finally, you need to know which claim centers can be used with which insurance plans:

 CREATE TABLE ClaimCenterPlan ( ClaimCenterID int, PlanID int ) 

Now, when a customer comes to a web site, you want to write a stored procedure that returns all the claim centers within the customer's city that uses his or her plan. In addition, if the customer doesn't have a plan, you want to show all the claim centers.

 CREATE PROC GetClaimCenterList        @City varchar(100), @State varchar(2), @CustomerID int=NULL AS BEGIN IF @CustomerID IS NULL SELECT * FROM ClaimCenter WHERE ClaimCenterCity = @City ELSE SELECT CC.* FROM Customer C INNER JOIN ClaimCenterPlan CCP ON CC.PlanID = CCP.PlanID INNER JOIN ClaimCenter CC on CC.ClaimCenterID = CCP.ClaimCenterID WHERE C.CustomerID = @CustomerID AND C.CustomerCity = @City END 

This provides the web page with a resultset that includes all the relevant claim center information, which is helpful for the user , and reduces the chance that customers will show up at a claim center that can't help them.

IN THE FIELD: WEB APPLICATIONS AND STORED PROCEDURES

If you've worked on web applications before, you've probably realized that most web programmers weren't hired to write good queries; they were hired to write good web pages. By encouraging, or perhaps mandating, the use of stored procedures, you can encapsulate all the queries that the web programmers write into one place with a consistent interface. Then, as the SQL guru you will be when you finish reading this book, you can go through and tweak their queries, make them more efficient, and leave the rowsets that get sent to their application and the parameter list the same. That way, you can rewrite the queries and leave the web pages completely alone.