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.


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


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


        SELECT TOP 20    
        , A.AddressLine1
        , A.City
        , A.PostalCode
        , SP.Name
        , CR.Name
    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"...
--Replaces: GetAddressesByCityName
EXECUTE AdventureWorks.Person.GetAddresses 
  @PartialCity = 'Birm'

--Get all addresses with a city name like "Birm" that are in Alabama...
--Replaces: GetAddressesByCityNameAndStateProvinceId
EXECUTE AdventureWorks.Person.GetAddresses 
    @PartialCity = 'Birm', 
    @StateProvinceId = 3

--Get all addresses in Alabama...
--Replaces: GetAddressesByStateProvinceId
EXECUTE AdventureWorks.Person.GetAddresses 
    @StateProvinceId = 3

--Get the address with AddressId = 49...
--Replaces: GetAddressById
EXECUTE AdventureWorks.Person.GetAddresses 
    @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.