views:

51

answers:

3

I wish to do something like this:

DECLARE @IgnoreNulls = 1;

SELECT Col1, Col2
FROM tblSimpleTable
IF @IgnoreNulls
BEGIN
  WHERE Col2 IS NOT NULL
END
ORDER BY Col1 DESC;

The idea is to, in a very PHP/ASP.NET-ish kinda way, only filter NULLs if the user wishes to. Is this possible in T-SQL? Or do we need one large IF block like so:

IF @IgnoreNulls
BEGIN
  SELECT Col1, Col2
  FROM tblSimpleTable
  WHERE Col2 IS NOT NULL
  ORDER BY Col1 DESC;
END
ELSE
BEGIN
  SELECT Col1, Col2
  FROM tblSimpleTable
  ORDER BY Col1 DESC;
END
+4  A: 

You can do that this way:

SELECT Col1, Col2
FROM tblSimpleTable
WHERE ( @IgnoreNulls != 1 OR Col2 IS NOT NULL )
ORDER BY Col1 DESC
David
+1  A: 

In general (unless the table is small) the best approach is to separate out the cases and do something like you have in your question.

IF (@IgnoreNulls = 1)
BEGIN
  SELECT Col1, Col2
  FROM tblSimpleTable
  WHERE Col2 IS NOT NULL
  ORDER BY Col1 DESC;
END
ELSE
BEGIN
  SELECT Col1, Col2
  FROM tblSimpleTable
  ORDER BY Col1 DESC;
END

This is less likely to cause you problems with sub optimal query plans being cached.

Martin Smith
+1, if there is only one parameter, like the questions seems to indicate, this looks like a good solution. [Dynamic Search Conditions in T-SQL - Using IF statements](http://www.sommarskog.se/dyn-search-2005.html#IF)
KM
+3  A: 

Dynamically changing searches based on the given parameters is a complicated subject and doing it one way over another, even with only a very slight difference, can have massive performance implications. The key is to use an index, ignore compact code, ignore worrying about repeating code, you must make a good query execution plan (use an index).

Read this and consider all the methods. Your best method will depend on your parameters, your data, your schema, and your actual usage:

Dynamic Search Conditions in T-SQL by by Erland Sommarskog

KM
Luckily, the tables I'm working with are small. The link does provide some good insight into the topic, though.
Onion-Knight
@Onion-Knight , learn the proper way to do things, and always do it that way, and when your data grows, you'll be in good shape. Otherwise you'll have bad habits and slow queries when you have big tables.
KM