我们可以将null传递给sql参数来查询所有吗？

I have a query as follows

我有一个查询如下

select * from table where col1 = @param1 and col2 = @parm2

And another

而另一个

select * from table where col1 = @param1

Is it possible do both operations in same query based on parameter passed, if it is null query all or when parameter has value select them.

是否可以基于传递的参数在同一查询中执行这两个操作，如果它是null query all或者当参数有值时选择它们。

My queries are very big and i have to create 2 versions of sp's for each and i was thinking that can i try to avoid creating two.

我的查询非常大，我必须为每个创建2个sp版本，我想我可以尝试避免创建两个。

6 个解决方案

#1

SELECT * from table where col1 = @param1 and col2 = isnull(@parm2, col2)

ought to do what you're looking for.

应该做你想要的。

#2

Well, you can try this, but I don't think it will be very performant:

好吧，你可以尝试这个，但我不认为它会非常高效：

SELECT * FROM tab WHERE col1 = @param1 AND col2 = ISNULL(@parm2, col2)

#3

You could try something like:

你可以尝试类似的东西：

    select * from table where coalesce(@param1, col1) = col1 
and coalesce(@param2, col2) = col2

#4

All the suggestions here about using COALESCE or ISNULL will work - effectively doing this:

这里关于使用COALESCE或ISNULL的所有建议都可行 - 有效地做到了这一点：

select *
from table
where (@param1 IS NULL OR col1 = @param1)
    and (@parm2 IS NULL OR col2 = @parm2)

But you may need to watch out for parameter sniffing. SQL Server 2005 does not have the OPTIMIZE FOR UNKNOWN - you can mask the parameters into local variables in the SP to help avoid that or use RECOMPILE option.

但您可能需要注意参数嗅探。 SQL Server 2005没有OPTIMIZE FOR UNKNOWN - 您可以将参数屏蔽到SP中的本地变量以帮助避免或使用RECOMPILE选项。

#5

How about this:

这个怎么样：

select *
  from table
  where where (col1 = @param1 and col2 = @parm2)
  or (col1 = @param1 and parm2 is null)

#6

If you use Stored procedures!

如果您使用存储过程！

IF Boolean_expression 
     { sql_statement | statement_block } 
[ ELSE 
     { sql_statement | statement_block } ]

In your scenario. something like

在您的方案中。就像是

if (@param1 = null)
Begin
 select * from table where col2 = @parm2
( 
End

else if (@param1 = 'something' )
Begin
(
 select * from table where col1 = @param1
End

reference : http://msdn.microsoft.com/en-us/library/ms182717.aspx

参考：http：//msdn.microsoft.com/en-us/library/ms182717.aspx

#1

SELECT * from table where col1 = @param1 and col2 = isnull(@parm2, col2)