views:

145

answers:

4

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?

+3  A: 

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.

Gabriel McAdams
+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
@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
+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
Also, if you find your query is longer than 4000 characters, use NVARCHAR(MAX), which will give you 2GB size (sql 2008)
Russell
One other note on using dynamic sql: If you try concatenation, both variables MUST be the same size
Gabriel McAdams
Please remember to accept this answer if you found it helpful.
Gabriel McAdams
Excellent - Gabriel, thx for all the options! #2 I'm reading from a small table (<7K rows) so legibiltiy and ease of maintenance are highest concerns. Thx again - I picked up some great techniques!
Val
+2  A: 

"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 ...
Mark Byers
+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
yes, this is my preferred solution. I picked Gabriel's answer, tho, since he gave me so many options, incl. this one.
Val
A: 

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.

tvanfosson
This is what I would do if performance were not a major concern (i.e. the flag columns are not indexed).
Aaronaught
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
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
+2  A: 

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(@fooFlag, 1))
AND (barFlag >= ISNULL(@barFlag, 0) AND barFlag <= ISNULL(@barFlag, 1))
AND (bazFlag >= ISNULL(@bazFlag, 0) AND bazFlag <= ISNULL(@bazFlag, 1))
AND (quuxFlag >= ISNULL(@quuxFlag, 0) AND quuxFlag <= ISNULL(@quuxFlag, 1))

The good thing about this query is that, because the possible values for "flags" are bounded, you can calculate all your conditionals as prerequisites instead of wrapping columns in them. This guarantees a high-performance index seek on whichever columns are indexed, and doesn't require writing any dynamic SQL. And it's better than writing 4 separate queries for obvious reasons.

Aaronaught
This doesnt guarantee a high-performance index seek as it will re-evaluate barFlag >= ISNULL(@barFlag, 0) each time.
Russell
@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
Apologies, Aaron, I was using the query with the IS NULL OR .. expressions :) Evaluating numeric expressions doesnt appear to do that. Sorry
Russell
No, it doesn't. Nothing is getting re-evaluated, the `ISNULL` is evaluated once at the beginning of the query.
Aaronaught
@Russel, no worries, this stuff confused me too for a very long time!
Aaronaught
This is also very good, and if I had to worry about performance I'd probably use it. I opted for a a simpler construct : (@flag = 'foo' and fooFlag = 1) OR (@flag = 'bar' and barFlag = 1)...
Val