selective filtering, part iii - dynamic sql filtering

i just thought of this as i posted my last entry and wanted to get it 'on paper' before it slips my mind.

if you are using dynamic sql to filter a query based on an optional/nullable parameter, you have to provide something to your WHERE clause in the case that you receive all nulls. here is an example that will not work using the clever ISNULL technique i discussed in my first post:

DECLARE @sSQL nvarchar(4000)

SET @sSQL = 'SELECT col_a FROM table_a '
SET @sSQL = @sSQL + 'WHERE col_b = ''' + ISNULL(@param1, 'col_b') + ''''

exec sp_executesql @sSQL
GO

what's the problem here? if @param1 is null, the WHERE clause is comparing the value of col_b to a constant, 'col_b', instead of setting it equal to itself. here is a correct way to accomplish the filter:

DECLARE @sSQL nvarchar(4000)

SET @sSQL = 'SELECT col_a FROM table_a '
IF @param1 IS NOT NULL
SET @sSQL = @sSQL + ' WHERE col_b = ''' + @param1 + ''''

exec sp_executesql @sSQL
GO

in this case, you only filter if the @param1 contains a value. however, this once again presents a problem - what if there are multiple optional/nullable parameters provided? without knowing which ones contain values, you don't know where to put the WHERE. for example:

DECLARE @sSQL nvarchar(4000)

SET @sSQL = 'SELECT col_a FROM table_a '
IF @param1 IS NOT NULL
SET @sSQL = @sSQL + ' WHERE col_b = ''' + @param1 + ''''
IF @param2 IS NOT NULL
SET @sSQL = @sSQL + ' WHERE col_c = ''' + @param2+ ''''

exec sp_executesql @sSQL
GO

this obviously breaks if both @param1 and @param2 are not null since you can't specify WHERE more than once. there are two options: write your query such that every combination of parameters containing and not containing values is accounted for (which is exponentially more difficult with each additional parameter), or you can be clever. let's rewrite the above sample:

DECLARE @sSQL nvarchar(4000)

SET @sSQL = 'SELECT col_a FROM table_a '
SET @sSQL = @sSQL + 'WHERE 1=1 '
IF @param1 IS NOT NULL
SET @sSQL = @sSQL + ' AND col_b = ''' + @param1 + ''''
IF @param2 IS NOT NULL
SET @sSQL = @sSQL + ' AND col_c = ''' + @param2+ ''''

exec sp_executesql @sSQL
GO

now that you've specified a condition that's always true (WHERE 1=1), you can dynamically add additional filters as needed. pretty damn slick if i do say so myself

one last thing - i always declare @sSQL as nvarchar(4000) in my examples because sp_executesql only works with variables with data types of nchar, ntext and nvarchar and the maximum size of an nvarchar variable used with sp_executesql is 4000