SQL Query在where子句中传递null和value [英] SQL Query passing null and value in where clause

问题描述

我有一个带有2个输入参数的存储过程。一个是Type，另一个是Institute。



Institue可能在某个时候为null。因此，如果该研究所为空，则不应该处于何种状态。



例如，我应该有如下查询。我可以使用单个查询吗？不使用IF。

I have a stored Procedure with 2 input parameters. one is Type and other is Institute.

Institue may be null sometime. So if the institute is null it should not be in where condition.

For example I should have query like below. Can I do with single query? Not using IF.

IF @type =1  and @Inst = 'abc'

Select * from Test
where Type = @type and Institute = @Inst 

If @type =1  and @Inst = null

Select * from Test
where Type = @type 

我需要上面的例子使用IF coniditon进行一次选择。可能吗？



感谢您的帮助。

I need the above example with one select with out using IF coniditon. IS that possible?

Thanks for your help.

推荐答案

Select * from Test
where Type = @type and (Institute = @Inst OR @Intst IS NULL)

select * from Test
where Type = @type and 
(coalesce(@inst, 1) = 1  or Institute = @inst)

coalesce返回其参数的第一个非空，所以当@inst为null时，您的条件将类似于Type = @type和（1 = 1或Institute = null）。

@inst有值的情况是Type = @type和（'abc'= 1或Institute ='abc'）。

我希望这有帮助。

coalesce returns first of its argument which is not null, so when @inst is null your condition will be like Type = @type and (1=1 or Institute = null).
The case when @inst has value will be Type = @type and ('abc' = 1 or Institute = 'abc').
I hope this helps.

您好，



查看以下链接...



你会得到关于NULL的想法



处理空值 [ ^ ]

在SQL Server 2005中处理NULL值 [ ^ ]

NULL，NULL，NULL，只有NULL [ ^ ]

空函数 [ ^ ]

一个益智 - 带有空的乐趣 [ ^ ]



问候，

GVPrabu

Hi,

Check the following links...

you will get Idea about NULL

Handling Null Values [^]
Handling NULL values in SQL Server 2005[^]
NULL, NULL, NULL and nothing but NULL[^]
NULL Functions[^]
A Puzzle – Fun with NULL[^]

Regards,
GVPrabu

这篇关于SQL Query在where子句中传递null和value的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！