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

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.

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.

1 - Personally I'd use the CASE approach as I think it's the neatest, though I'd more likely use a simple case rather than a searched case as you've shown as the thing to be matched is constant (i.e. CASE @flag WHEN 'foo' THEN fooFlag WHEN 'bar' then barFlag END = 1) – Greg Beech Dec 30 '09 at 3:13 @Greg: Of course, that's likely to have the worst performance of all the options, as you are now not only wrapping one column in a function, you are wrapping all of them. If you have a different index on each column then this becomes a table scan.

– Aaronaught Dec 30 '09 at 3:17 +1 - depending on the complexity of the query. Although in my experience even when the query starts off simple it always ends up (during maintenance etc) complex, and I wished I had started with dynamic sql. – Russell Dec 30 '09 at 3:20 Also, if you find your query is longer than 4000 characters, use NVARCHAR(MAX), which will give you 2GB size (sql 2008) – Russell Dec 30 '09 at 3:20 One other note on using dynamic sql: If you try concatenation, both variables MUST be the same size – Gabriel McAdams Dec 30 '09 at 3:24.

Alternately, I could have 4 copies of the query and select among them in a CASE statement. " You don't need to copy your entire query 4 times, just add all the possibilities into the where clauses in your single copy of the query: select ID, name, description, ... from myTable where (@flag = 'foo' and fooFlag = 1) OR (@flag = 'bar' and barFlag = 1) OR ...

1 +1: That would be my approach, though I'd still use dynamic so all the ORs aren't dragged along every time the query runs. – OMG Ponies Dec 30 '09 at 3:05 yes, this is my preferred solution. I picked Gabriel's answer, tho, since he gave me so many options, incl.

This one. – Val Dec 30 '09 at 18:18.

What I would do is CASE some variables at the beginning. Example: DECLARE @fooFlag int, @barFlag int, @bazFlag int, @quuxFlag int SET @fooFlag = CASE WHEN @flag = 'foo' THEN 1 ELSE NULL END SET @barFlag = CASE WHEN @flag = 'bar' THEN 1 ELSE NULL END SET @bazFlag = CASE WHEN @flag = 'baz' THEN 1 ELSE NULL END SET @quuxFlag = CASE WHEN @flag = 'quux' THEN 1 ELSE NULL END SELECT ID, name, description, ... FROM myTable WHERE (fooFlag >= ISNULL(@fooFlag, 0) AND fooFlag = ISNULL(@barFlag, 0) AND barFlag = ISNULL(@bazFlag, 0) AND bazFlag = ISNULL(@quuxFlag, 0) AND quuxFlag And it's better than writing 4 separate queries for obvious reasons.

This doesn't guarantee a high-performance index seek as it will re-evaluate barFlag >= ISNULL(@barFlag, 0) each time. – Russell Dec 30 '09 at 3:17 @barFlag is just a scalar variable, not a table column. It's sargable - you could expand the entire expression into 8 separate constant flags.

Try it and see. – Aaronaught Dec 30 '09 at 3:21 Apologies, Aaron, I was using the query with the IS NULL OR .. expressions :) Evaluating numeric expressions doesn't appear to do that. Sorry – Russell Dec 30 '09 at 3:22 1 No, it doesn't.

Nothing is getting re-evaluated, the ISNULL is evaluated once at the beginning of the query. – Aaronaught Dec 30 '09 at 3:23 @Russel, no worries, this stuff confused me too for a very long time! – Aaronaught Dec 30 '09 at 3:27.

You could have a parameter for each possible flag column, then check if the parameter is null or the value in the column is equal to the parameter. Then you pass in a 1 for the flags that you want to check and leave the others null. Select id, name, description, ... from myTable where (@fooFlag is null or fooFlag = @fooFlag) AND (@barFlag is null or barFlag = @barFlag) AND ... Honestly, though, this seems like an ideal candidate for building a dynamic LINQ query and skipping the SPROC once you get to SQL2008.

This is what I would do if performance were not a major concern (i.e. The flag columns are not indexed). – Aaronaught Dec 30 '09 at 3:09 No, I'd really like to avoid multiple params -- more maintenance if when I expand to more flags.

With a single param, the maintenance is restricted to the SP; if I add params, I have to change the structure of calling code, too, rather than just pass new values in an existing param. Thanks, tho! – Val Dec 30 '09 at 18:20 Oh, yeah -- forgot to mention, tho I'm moving the DB to SQL2008, my app is ASP Classic, and I'm not going to have a chance to move this part of the app to .

NET anytime soon. So LINQ is out... – Val Dec 30 '09 at 18:21.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.