C4SC Update

01.27.2011 06:25 by kbeckman | Comments

I wanted to post a quick update to the C4SC audience and inform everyone about some recent site enhancements as well as some upcoming post changes, etc.


.NET 4.0, BlogEngine.NET 2.0

C4SC was redeployed this evening and is now running entirely on the .NET 4.0 platform! While nothing should change the user experience, it’s always nice to advertise major upgrades. Along with the .NET upgrade, C4SC has moved from BlogEngine.NET version 1.61 to the BlogEngine.NET 2.0 platform. I’m really excited about the new features and functions added by the BlogEngine.NET team and highly encourage anyone thinking about building a blog site to use BlogEngine.NET. The 2.0 upgrade has introduced some great features to enhance the administration experience.


Syntax Highlighting

I should have paid closer attention to the BlogEngine.NET roadmap... Then I might have noticed the development team was planning the implementation of Alex Gorbathev’s SyntaxHighlighter as a BlogEngine.NET extension. I had recently implemented this in C4SC, but have now scrapped my custom implementation in favor of the included extension. From now on all C4SC code posts will have well-formatted syntax rather than just plain text. I plan on updating older posts to accommodate the SyntaxHighlighter changes as time permits.


New Format for Refactoring-Related Posts

All posts related to any type of refactoring will resemble the format used by Martin Fowler in his book, Refactoring: Improving the Design of Existing Code. I consider that to the the single most beneficial book I’ve ever read on software development and will begin to follow the same style in this blog. Each post will include four sections detailing the problem and formulating a solution – Description, Motivation, Mechanics and Example(s).  It should be a good way to accumulate a targeted catalog of refactorings I’ve used and continue to use throughout my software development.

T-SQL Sproc Optional Parameters Using COALESCE

01.26.2011 22:42 by kbeckman | Comments


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.