SQL Server Tips – Optional Parameters and Find Closest Match

It’s funny how you can survive for years not knowing some really simple stuff.  I learned two useful things last week.  For instance…

Optional SQL Parameters

To have an ‘optional’ parameter in a SQL ‘search’ Stored Proc you’d often declare a parameter thus…

@MyParam VARCHAR(20) = NULL –to denote that we want to ignore this param

and you’d probably

  1. have some convaluted logic to possibly build dynamic SQL around whether to factor the parameter into your query
  2. build some nasty ‘like’ clause that defaults to ‘%’
  3. derive MIN and MAX values from the input parameter and use a BETWEEN
  4. use a ‘magic value’ in place of your NULL to denote ‘ignore’
  5. something else equally ugly I’ve not considered

…or (assuming this parameter is expecting to do a direct match) you could just simply do

WHERE @MyParam = ISNULL(@MyParam, MyFieldIWantToMatch)

This will ensure if your field is nulled out it just does a simple (IF 1 = 1) type thing.


Find Closest Match

I had a need to find the closest match to a particular value within a 10% tolerance +-.  The following does that fairly simply (I did nick the idea from thescripts.com) but these sorts of things do avoid the need for nasty tempdb use with temp tables…

The following is an example – so don’t think I design tables with one column!


SET @Amount = 50


SET @MinAmount = @Amount * 0.9
SET @MaxAmount = @Amount * 1.1

FROM AT.AmountTable AT
WHERE Amount BETWEEN @MinAmount AND @MaxAmount –within range
ORDER BY ABS(@Amount – AT.Amount) ASC –Nifty ‘closest’ match

You can then take the first row in your result set to be the closest match.