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.