Filtering Data Using Stored Procedures

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.

Implementing Error Handling in Stored Procedures

• Implement error handling in stored procedures.

Stored procedures can also manage some error conditions, whether caused by data exceptions or by problems with SQL Server. There are ways to handle errors inside a stored procedure.

First of all, judicious use of return codes is recommended to ensure that the applications upstream understand the execution status of the stored procedure. Within SQL Server, using the integer return codes is a good way to make sure that calling stored procedures understand how called stored procedures completed their tasks . It is important to create written standards on how return codes should be used, such as whether the return value is a "success" value or a "failed" value. Some systems use as success, so that the other numbers are available to denote specific types of errors.

Next , make sure you are using transactions, @@TRANCOUNT , and ROLLBACK correctly within the stored procedure. That's all covered back in Chapter 6.

Some T-SQL statements are more likely to cause errors than others, such as statements that perform BULK INSERT operations or that interact with other servers using the Distributed Transaction Coordinator. To discover whether a particular T-SQL statement was successful, you can check the value of the @@ERROR global variable. This variable is set similar to how @@ROWCOUNT is set in that it is valid only if you check it immediately following the statement that caused the error. If the @@ERROR variable is set to zero, then no error occurred during execution of the preceding statement. If @@ERROR is set to something other than zero, then an error occurred during the attempt to run the statement. The @@ERROR variable can be used to handle errors that won't stop a batch (or a stored procedure) from running, such as constraint violations or problems with aggregate functions that don't have enough data.

Here's an example of what error handling in a stored procedure might look like:

CREATE PROC errhandler AS
BEGIN
DECLARE @ErrorCollector int
BEGIN TRANSACTION
INSERT INTO mytable VALUES (1, 4, 3, 19)
       SET @ErrorCollector = @@ERROR
IF @ErrorCollector <> 0
begin
ROLLBACK TRANSACTION
RETURN @ERRORCOLLECTOR
end
else
COMMIT TRANSACTION
return 0
END

A few notes on this. First of all, using BEGIN and END to wrap the whole thing up is not required; it's just another style you can use. The @ErrorCollector variable is used to hold the @@ERROR value for the return code, if needed. The function also uses transactions to handle data integrity, and returns a non-zero number if the stored procedure fails, which represents the error code.

Stored procedures have two options that can be set when they are being created or altered : ENCRYPTION and RECOMPILE . The ENCRYPTION option specifies that the text of the stored procedure should not be available in syscomments or to the sp_helptext stored procedure. This is typically done when you are distributing proprietary code to customers and you don't want them to be able to see your code. It also typically has to be removed before you install version upgrades and some service pack upgrades for SQL Server, which is a good reason to not use it, because removing the encryption means re-creating all the stored procedures.

The RECOMPILE option specifies that the stored procedure's execution plan should be recompiled before it is executed. This is used when the queries in the stored procedure change significantly based on incoming parameters ”changes that would change the indexes or join strategies SQL Server uses. To use either of these options, the syntax is:

create procedure myproc
@MyParameter int
with recompile
as
       do stuff here
go

Whenever a user executes a stored procedure, SQL Server checks the stored procedure cache, which is a part of the memory that SQL Server uses for disk cache, to see whether the stored procedure execution plan is present in the cache. If it is, then SQL Server uses the cache to avoid having to figure out the execution plan again. The problem is that sometimes you want SQL Server to re-figure the execution plan. For example, if the stored procedure references tables that have indexes that change, or if the stored procedure just has an extremely convoluted plan, you may want to force a recompile by creating the stored procedure with the RECOMPILE option. If the stored procedure has already been created without the RECOMPILE option, you can change it to recompile on execution by using the sp_recompile system stored procedure. If you want to recompile the plan just once, you can use the WITH RECOMPILE option for the EXEC statement to execute the stored procedure ”just tack WITH RECOMPILE onto the end of the EXEC .