Software Vendor Mistakes With SQL Server: Handling Optional Parameters

Optionally Yours


You’ve got tables. So many tables And columns. So many columns.

Users — demanding as they are — might wanna see, search, and order by all sorts of things in those many columns in those many tables. Writing a query to do that is hard at first, especially if you’re afraid of dynamic SQL.

Over the years, developers have come up with all sorts of “workarounds” for handling optional search parameters. The problem is that none of them work, whether it’s:

  • column = @parameter or @parameter is null
  • column = isnull(@parameter, column)
  • column = coalesce(@parameter, column, ‘magic’)

Or any variation thereof. Doing this can (and will!) screw up query performance in all sorts of ways that sneak up on you.

  • Bad cardinality estimates
  • Scanning instead of Seeking in indexes
  • Using the “wrong” indexes

In the video below, I’ll show you how to use dynamic SQL the right way to handle optional parameter search scenarios. This video is a small part of my paid training. If you like what you see, there’s a link for 75% off the entire package at the bottom of the post.

Optional Parameters Video


Thanks for reading!

Going Further


If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.



2 thoughts on “Software Vendor Mistakes With SQL Server: Handling Optional Parameters

  1. I highly recommend taking Erik up on his training offer.
    I did, and I’m always amazed at how much more I can learn from others, even with developing databases my entire career. The custom software development company I work for (https://www.emergentsoftware.net) builds software that, you guessed it, also builds databases for the apps.

    Our client’s web app almost always requires an optional parameter search user interface. So instead of me creating the store procedure for each one, I built sp_CRUDGen (http://spcrudgen.org) that includes generating search stored procedures with dynamic TSQL. It is crazy! sp_CRUDGen generates dynamic TSQL, that generates dynamic TSQL, that executes the dynamic TSQL. I implemented a ton of things I learned in Erik’s training.

Comments are closed.