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
- have some convaluted logic to possibly build dynamic SQL around whether to factor the parameter into your query
- build some nasty ‘like’ clause that defaults to ‘%’
- derive MIN and MAX values from the input parameter and use a BETWEEN
- use a ‘magic value’ in place of your NULL to denote ‘ignore’
- 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!
DECLARE @Amount INT
SET @Amount = 50
DECLARE @MinAmount INT
DECLARE @MaxAmount INT
SET @MinAmount = @Amount * 0.9
SET @MaxAmount = @Amount * 1.1
SELECT AT.Amount
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.