如何有条件地过滤WHERE子句中的列? [英] How to conditionally filter on a column in a WHERE clause?

查看:183
本文介绍了如何有条件地过滤WHERE子句中的列?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

OK,第一个条件列问题:



我正在编写一个存储过程,它接收一个映射到多个标志列之一的输入参数。过滤所请求的列的最佳方法是什么?我目前在SQL2000上,但将要迁移到SQL2008,所以我将采取当代解决方案(如果有的话)。



在sproc中查询的表格看起来像

  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

我想做一些像



来自myTable

其中(colname like @flag +'Flag')= 1

所以如果我像调用sproc exec uspMyProc @flag ='foo'我会得到第1行和第4行。



我知道我不能直接在SQL中做括号。为了做动态SQL,我必须将整个查询填充到一个字符串中,连接WHERE子句中的@flag param,然后执行该字符串。除了在做动态SQL时我感到脏的感觉,我的查询是相当大的(我选择了几十个字段,加入了5个表,调用了几个函数),所以它是一个巨大的字符串,因为单行在3行WHERE过滤器中。



另外,我可以有4个查询副本,并在CASE语句中选择它们。这使得SQL代码可以直接执行(并且受到语法高亮等等的限制),但代价是重复大块代码,因为我不能在WHERE子句上使用CASE。



还有其他选择吗?可以应用的棘手连接或逻辑操作?或者我应该只是解决它并执行动态SQL?

解决方案

有几种方法可以这样做:



您可以使用案例陈述来执行此操作。

 选择ID,描述,... 
从myTable
其中CASE
WHEN @flag ='foo'然后fooFlag
WHEN @flag ='bar'然后barFlag
END = 1

您可以使用IF。

  IF(@flag ='foo')BEGIN 
从myTable
中选择ID,名称,描述...
其中fooFlag = 1
END ELSE IF(@flag ='bar')BEGIN
从myTable
中选择ID,名称,描述...
其中barFlag = 1
END

....

你可以有一个复杂的where子句,带有很多括号。 / p>

 选择ID,名称,说明,... 
from myTable
其中(@flag ='foo '和fooFlag = 1)
OR(@flag =' bar'和barFlag = 1)OR ...

您可以使用动态sql: ($)

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

EXECUTE sp_ExecuteSQL @SQL,N''

还有更多,但我认为其中一个会让你走。


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?

解决方案

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.

这篇关于如何有条件地过滤WHERE子句中的列?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆