Your codebase has a glut of repeated stored procedure logic for “get” or SELECT-based operations. Table JOINS, stored procedure parameters (for row filtering) and return types are similar among a focused set of stored procedures. The focused stored procedure group resembles the following pattern: GetEntityById, GetEntitiesByParentId, GetEntityByExactName, GetEntitiesByPartialName, GetEntityBySomeOtherParameterCombo.
Use the T-SQL COALESCE function in the procedure’s WHERE clause logic to implement optional parameters and combine the many similar, individual procedures into a single procedure with functionality for multiple input parameter combinations that yield a single return type.
I’m currently working in a codebase with an overwhelming amount of T-SQL stored procedures. There’s an individual stored procedure for every CRUD operation you can think of… Unfortunately, with this particular application, it doesn’t make a lot of sense to start swapping out stored procedures and ADO.NET code for a more sexy ORM. The existing data access patterns already work well and all of the developers are very familiar with it. In addition, there are areas of the codebase where the domain model is coupled a bit too tightly to the data access code. The refactoring effort to introduce an ORM would be more than the deadlines allow for. An alternative is to reduce the number of similar “get” stored procedures into a single procedure that provides all of your required functionality. This approach can significantly impact the overall procedure count and make both database management and stored procedure enhancement easier .
Creating a single stored procedure for all of your “get” operations is possible with WHERE clause usage of the T-SQL COALESCE function. COALESCE takes an expression list and applies the first item that is NOT NULL. The expression list allows for the addition of stored procedure parameters as well as column values. Initializing all of your optional parameters = NULL in the parameter declaration will initialize the parameter as NULL if a value is not given by a call to the procedure. Apply a filter in the WHERE clause using the COALESCE function that uses your optional parameter and provide an alternate expression (if your parameter evaluates to IS NULL) that will always logically evaluate to TRUE. This will make sense when you evaluate the example…
1) Create a new stored procedure that will replace all of the existing “get” procedures for a given entity. Name it with a generic name that doesn’t indicate what parameter combination is required – GetEntity.
2) Add all of the possible input parameters to the new procedure. All required parameters should be declared as you normally would declare them. All optional parameters should be initialized as NULL in the declaration.
CREATE PROCEDURE dbo.GetEntity
@EntityId INT = NULL
, @PartialEntityName NVARCHAR(30)
, @ParentEntityId INT = NULL
3) For all optional filtering parameters, use the T-SQL COALESCE keyword to build an expression in the WHERE clause that first uses your optional parameter if it is NOT NULL and some other filter value(s) if your optional parameter IS NULL.
WHERE SomeTable.EntityId = COALESCE(@EntityId, SomeTable.EntityId)
AND SomeTable.ParentEntityId = COALESCE(@ParentEntityId, SomeTable.ParentEntityId)
AND((@PartialEntityName IS NULL AND SomeTable.EntityName = SomeTable.EntityName)
OR SomeTable.EntityName LIKE '%' + @PartialEntityName + '%')
4) Replace the usage of an existing “get” procedure with your new stored procedure and test its behavior.
5) Make sure any related unit tests are passing.
6) Repeat steps 4 and 5 for any usages of your old “get” stored procedures.
7) Be sure to tune/update any existing indexes or create additional indexes to accommodate your search criteria.
This example uses the AdventureWorks sample DB in SQL 2008.
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
-- Author: Keith Beckman
-- Create date: 1/7/2011
-- Description: Selects complete address data based on a variety of input criteria.
CREATE PROCEDURE Person.GetAddresses
@AddressId INT = NULL
, @PartialCity NVARCHAR(30)
, @StateProvinceId INT = NULL
SET NOCOUNT ON;
SELECT TOP 20
FROM Person.Address A
INNER JOIN Person.StateProvince SP ON SP.StateProvinceID = A.StateProvinceID
INNER JOIN Person.CountryRegion CR ON CR.CountryRegionCode = SP.CountryRegionCode
WHERE A.AddressID = COALESCE(@AddressId, A.AddressID)
AND A.StateProvinceID = COALESCE(@StateProvinceId, A.StateProvinceID)
AND((@PartialCity IS NULL AND A.City = A.City) OR A.City LIKE '%' + @PartialCity + '%')
Here’s some usage examples to get an idea of what’s possible with the dynamic input parameter stored procedure approach…
--Get all addresses with a city name like "Birm"...
@PartialCity = 'Birm'
--Get all addresses with a city name like "Birm" that are in Alabama...
@PartialCity = 'Birm',
@StateProvinceId = 3
--Get all addresses in Alabama...
@StateProvinceId = 3
--Get the address with AddressId = 49...
@AddressId = 49
As you can see, there are numerous usages under this approach (certainly not limited to what I’ve shown above). The best part about this is that you’ve significantly reduced the amount of similar “Get” procedures you need to write! Any time you need to add additional JOINS or WHERE clauses, you can add them in a single procedure rather than replacing logic that’s scattered all over your codebase.
Now you and your DBA can call it a day… He/she owes you a beer after this one. Or if they helped you to arrive at this approach like mine did – you owe them one.