忽略null的参数 [英] Ignore parameters which are null

问题描述

我正在尝试从Web应用程序运行存储过程。我有OR条件的多个参数，但我想在查询执行时忽略用户留空的参数。



我试过的：



这就是我想要的

I am trying to run a stored proc from a web application. I have multiple parameters with OR condition but i want the parameters which are left blank by the user to be ignored when the query execute.

What I have tried:

This is what i am trying

ALTER Proc [dbo].[spStatusReport]
@CUST_NAME varchar(20),
@CUST_CODE   varchar(10) = NULL 
as 
SELECT  CUST_CODE, DEPT_CODE,EMPLOYEE_CODE, EST_SCRAP, EXCH_RATE,
F_G_, LINE4, LOCATION
FROM Status_Report 
WHERE 
((@CUST_NAME IS NULL OR [LOCATION] LIKE '%' + @CUST_NAME + '%') OR
(@CUST_CODE IS NULL OR [CUST_CODE] LIKE '%' + @CUST_CODE + '%'))

但是当我运行

But when i run

EXEC spStatusReport 'ABC',''

我得到所有东西而不只是ABC位置

I get everything instead of only the ABC location

推荐答案

再看一下你的 WHERE 条款：

Take another look at your WHERE clause:

    (@CUST_NAME IS NULL OR [LOCATION] LIKE '%' + @CUST_NAME + '%')
OR
    (@CUST_CODE IS NULL OR [CUST_CODE] LIKE '%' + @CUST_CODE + '%')

您已使用或组合两个参数的过滤器。因此，如果 参数 NULL ，则会返回所有记录。



我怀疑你的意思是使用和代替：

You've used OR to combine the filters for both parameters. Therefore, if either parameter is NULL, then all records will be returned.

I suspect you mean to use And instead:

    (@CUST_NAME IS NULL OR [LOCATION] LIKE '%' + @CUST_NAME + '%')
AND
    (@CUST_CODE IS NULL OR [CUST_CODE] LIKE '%' + @CUST_CODE + '%')

您还需要了解 NULL 与空字符串之间的区别。在您的示例中，您传入一个空字符串，因此 @CUST_CODE IS NULL 将为false。因此， CUST_CODE 的任何行 NULL 将从您的结果中排除，因为它们不会通过 LIKE'%%'测试。



因此，我怀疑你需要：

You also need to appreciate the difference between NULL and an empty string. In your example, you're passing in an empty string, so @CUST_CODE IS NULL will be false. As a result, any rows where CUST_CODE is NULL will be excluded from your results, since they won't pass the LIKE '%%' test.

Therefore, I suspect you need:

    (@CUST_NAME IS NULL OR @CUST_NAME = '' OR [LOCATION] LIKE '%' + @CUST_NAME + '%')
AND
    (@CUST_CODE IS NULL OR @CUST_CODE = '' OR [CUST_CODE] LIKE '%' + @CUST_CODE + '%')

观察：



0）如果存储过程中的代码处理它，为什么还要指定默认值null？



2）为此指定一个空字符串第二个参数与指定 NULL 不同。



尝试将其更改为以下内容：



EXEC spStatusRepport @ CUST_NAME = 'ABC'，@ CUST_CODE = NULL

这篇关于忽略null的参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！