have you any wool?

Writing useful and reusable stored procedures is something I struggled with when first learning SQL. Not knowing anything about Execution Plans, I would often nest several “IF” statements in my procedure to execute different statements based on the parameters I wanted to pass in. This was my solution for dealing with a situation where I wanted to omit some parameters when I called the procedure, but didn’t know how to exclude them from my statements.

As I learned more and more about the language, I realized there were a couple different ways you could handle this situation and have settled with the following. Include all the parameters in your Where Clause, but also check to see if they’re null. Doing so allows you to use all the parameters at once, none of them, one of them, or any number in between.

Here is an example stored procedure built on the AdventureWorksLT database, which can be downloaded here. As always, this may or may not be the best practice solution, it’s just the solution I tend to use most. I’m always open to suggestions and debates on the the things I posted in the TOTD.

Create Procedure SalesLT.pCustomerAddressSearch
@City nvarchar(30) = null,
@StateProvince nvarchar(50) = null,
@CountryRegion nvarchar(50) = null,
@PostalCode nvarchar(15) = null

AS

Select
FirstName, LastName, AddressLine1, AddressLine2, City, StateProvince, CountryRegion, PostalCode
From
SalesLT.Customer C
Inner Join
SalesLT.CustomerAddress CA on CA.CustomerID = C.CustomerID
Inner Join
SalesLT.Address A on A.AddressID = CA.AddressID
Where
(City = @City or @City is null) AND
(StateProvince = @StateProvince or @StateProvince is null) AND
(CountryRegion = @CountryRegion or @CountryRegion is null) AND
(PostalCode = @PostalCode or @PostalCode is null)

Which then allows execution of the stored procedure in multiple ways.

Exec SalesLT.pCustomerAddressSearch @City = 'Ottawa', @PostalCode = 'K4B 1T7';

Exec SalesLT.pCustomerAddressSearch @City = 'Ottawa';

Exec SalesLT.pCustomerAddressSearch;

As I understand it this allows SQL Server to compile and save a proper execution plan which is not regenerated every time you run the procedure as can be the case when you’re placing IF statements in your procedures.

I spent a few minutes searching for a better way to post code on WordPress posts and found a pretty elegant solution. I took a few more minutes to update the existing TOTD posts, so you can now actually copy and paste them and have them functional without changes.

Sweeeeet.