Inline If Statements in SQL

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 N开发者_如何学GoULL
  ORDER BY Col1 DESC;
END
ELSE
BEGIN
  SELECT Col1, Col2
  FROM tblSimpleTable
  ORDER BY Col1 DESC;
END

You can do that this way:

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

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

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.