Modularizing Stored Procedures

by Mike Olkin

Some of the select statements used by GPV stored procedures are useful in more than one stored procedure. In Amherst, we like our Identify stored procedures to show everything that any associated DataTabs would show for a feature. Intially, we accomplished this by copying & pasting our DataTab stored procedure select statements into our Identify stored procedures, changing the @parcelid parameter to a @gpin parameter. This become quite messy after a while.

Fortunately, there is a better & much simpler way. The following example is of an Identify stored procedure that executes three associated DataTab stored procedures. A header is placed before each one in order to delineate between types of information:

CREATE PROCEDURE GPV.GPVIdentify_Parcel
 @gpin nvarchar(20)
AS
SELECT 'Property Information' as Header
EXEC GPVDataTab_ParcelBasic @gpin
SELECT 'Sales History' as Header
EXEC GPVDataTab_ParcelSales @gpin
SELECT 'Permit History' as Header
EXEC GPVDataTab_ParcelPermits @gpin
GO