How to conditionally filter on a column in a WHERE clause?

Question

OK, the umpteenth conditional column question:

I'm writing a stored proc that takes an input parameter that's mapped to one of several flag columns. What's the best way to filter on the requested column? I'm currently on SQL2000, but about to move to SQL2008, so I'll take a contemporary solution if one's available.

The table queried in the sproc looks like

ID ...  fooFlag  barFlag  bazFlag  quuxFlag
--      -------  -------  -------  --------
01         1        0       0          1
02         0        1       0          0
03         0        0       1          1
04         1        0       0          0

and I want to do something like

select ID, name, description, ...
from myTable
where (colname like @flag + 'Flag') = 1

so if I call the sproc like exec uspMyProc @flag = 'foo' I'd get back rows 1 and 4.

I know I can't do the part in parens directly in SQL. In order to do dynamic SQL, I'll have to stuff the entire query into a string, concatenate the @flag param in the WHERE clause and then exec the string. Aside from the dirty feeling I get when doing dynamic SQL, my query is fairly large (I'm selecting a couple dozen fields, joining 5 tables, calling a couple of functions), so it's a big giant string all because of a single line in a 3-line WHERE filter.

Alternately, I could have 4 copies of the query and select among them in a CASE statement. This leaves the SQL code directly executable (and subject to syntax hilighting, etc.) but at the cost of repeating big chunks of code, since I can't use the CASE on just the WHERE clause.

Are there any other options? Any tricky joins or logical operations that can be applied? Or should I just get over it and exec the dynamic SQL?

Answer 1

There are a few ways to do this:

You can do this with a case statement.

select ID, name, description, ...
from myTable
where CASE
    WHEN @flag = 'foo' then fooFlag
    WHEN @flag = 'bar' then barFlag
END = 1

You can use IF.

IF (@flag = 'foo') BEGIN
    select ID, name, description, ...
    from myTable
    where fooFlag = 1
END ELSE IF (@flag = 'bar') BEGIN
    select ID, name, description, ...
    from myTable
    where barFlag = 1
END

....

You can have a complicated where clause with a lot of parentheses.

select ID, name, description, ...
from myTable
where (@flag = 'foo' and fooFlag = 1)
OR (@flag = 'bar' and barFlag = 1) OR ...

You can do this with dynamic sql:

DECLARE @SQL nvarchar(4000)

SELECT @SQL = N'select ID, name, description, ...
from myTable
where (colname like ''' + @flag + 'Flag'') = 1'

EXECUTE sp_ExecuteSQL @SQL, N''

There are more, but I think one of these will get you going.