SQL参数空值 [英] SQL parameter null value

问题描述

大家好，

我正在使用asp.net和SQL作为后端来制作一个客户服务网站.我被困在一个存储过程中，无法得到投诉.

实际情况:

有一页可以解决所有投诉.在该页面上有一个搜索框，通过该框可以使用date，to date，投诉编号，投诉区域来搜索投诉.现在，如果没有搜索条件，则每个投诉都应加载到gridview中.

现在的问题是我在sqlstoredprocedure中使用了4个参数，这些参数可以是某些值或可以为null.现在我需要检查参数的每种可能性(即它们是否可以为null或可以是某些值)，以便可以根据搜索条件填充gridview.现在我已经使用SQL存储过程中的"if"子句来满足这些条件，问题是如果我使用4个参数，那么可能会有4 * 4 = 16种可能性，而我必须在存储过程中使用16次if子句，这是我认为非常奇怪的 .还有其他方法吗..

我不想在此使用内联查询...请建议仅使用存储过程的某种方式.....

请帮帮我..

谢谢与问候，
Krunal Panchal

Hi everyone,

I am making an website of Customer care using asp.net and SQL as backend. I am stuck up at one storedprocedure of getting complaints .

Actual scenario :

there is one page for getting all complaints. In that page there is a search box by which complaints could be searched using from date , to date, complaint number,complaint region. Now if there is no search criteria every complaint should be loaded in gridview.

now the problem here is i used 4 parameters in sql storedprocedure which could be either some value or could be null. Now i need to check each and every possibility of parameters (i.e whether they could be null or could be some value) so that i could fill the gridview according to the search criteria. Now i have used "if" clause in SQL storedprocedure for fulfilling these criteria the problem is if i use 4 parameters then there could be 4*4 = 16 possibilities and i have to use 16 times if clause in storedprocedure and this is i think very weird . Is there any other way to do so..

I dont want to use inline query in doing so... Please suggest some way using Stored procedures only.....

Please help me out..

Thanks & Regards,
Krunal Panchal

推荐答案

如果我正确理解了您的问题，无论值是否为null，您都希望在WHERE子句中使用相同的条件.
如果上述是正确的，则可以使用例如COALESCE.例如，如果您的表中有一个字段Col1，并且知道-1值永远不在行中，则可以使用类似
的条件

If I understood correctly your question, you want to use the same condition in WHERE clause regardless if the value is null.

If the above is true, you can use for example COALESCE. For example if you have a field Col1 in your table and you know that -1 value is never in the rows, then you could use a condition like

WHERE ...
AND COALESCE( Col1, -1) = COALESCE( @parameter, -1)
... 

但是，这可能会对性能造成影响，因此请对其进行彻底的测试.

However, that can cause a performance impact so test it thoroughly.

您可以使用合并来完成此操作.这是一个使用它的示例

You can do this by using the coalesce. here is an example to use it

select * from table
where
column1 = coalesce(@param1, column) AND
column2 = coalesce(@param2, column) AND 
column3 = coalesce(@param3, column) AND
column4 = coalesce(@param4, column)

只需阅读一下.

Just read about it. it is quite straightforward and pretty efficient to use.

看看这个查询的结果:

Take a look at results of this query:

DECLARE @par1 INT 
DECLARE @par2 MONEY 
DECLARE @par3 NVARCHAR(10) 
DECLARE @par4 INT 

SET @par1 =100
SET @par2 = 0.2
SET @par3 = 'Some text'
SET @par4 = NULL

SELECT COALESCE(@par1,0),COALESCE(@par2,0),COALESCE(@par3,''),COALESCE(@par4,0)

您看到了什么?

以您的示例为例:

What you see?

With your example:

SELECT CAST(COALESCE(hourly_wage * 2, salary, commission * num_sales) AS money) AS 'Total Salary' 
FROM #wages
WHERE [hourly_wage] IS NULL OR [salary] IS NULL OR [commission] IS NULL OR [num_sales] IS NULL
ORDER BY 'Total Salary';

或者只是:

Or just:

SELECT CAST(COALESCE(hourly_wage * 2, salary, commission * num_sales) AS money) AS 'Total Salary' 
FROM #wages
ORDER BY 'Total Salary';

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