从具有空值的搜索中查询数据库

I am using asp .net web form search in c#, for example with fields: - name - fruit

我在c#中使用asp .net web表单搜索,例如使用字段: - name - fruit

I want to write a sproc that can search name or fruit. If I knew that both would have values, I would pass two parameters into the sproc and would write:

我想写一个可以搜索名字或水果的sproc。如果我知道两者都有值,我会将两个参数传递给sproc并写入:

Select * from myTable where name =@name and fruit = @fruit

However sometimes, either name or fruit could be empty. Without writing a separate search for fruit and name. How would I achieve this?

但有时候,名字或水果都可能是空的。没有单独搜索水果和名称。我怎么做到这一点?

I would prefer not to create a dynamic query if possible, but if this is the only way how would I write it?

如果可能的话,我宁愿不创建动态查询,但如果这是我写的唯一方法呢?

4 个解决方案

#1

You'll need to make sure that empty strings aren't passed as arguments instead of null, but the following should work. I'm pretty sure, though, that if the user doesn't input anything the form value will be null.

您需要确保空字符串不作为参数而不是null传递,但以下内容应该有效。但我很确定,如果用户没有输入任何内容,那么表单值将为null。

 select * from myTable
 where (@name is null or name = @name) and (@fruit is null or fruit = @fruit)

#2

maybe:

SELECT * FROM myTable WHERE name Like '%' + @name AND fruit LIKE '%' + @fruit

#3

select * from myTable 
where 
   (name = @name and fruit = @fruit)
   or (name = @name and @fruit is null)
   or (fruit = @fruit and @name = is null )

#4

select * from myTable where name = isnull(@name, name) and fruit = isnull(@fruit, fruit)

Will return the entire table if both @name and @fruit is null. If that is a problem, you better add some additional logic to the SP.

如果@name和@fruit都为null,则返回整个表。如果这是一个问题,最好为SP添加一些额外的逻辑。

#1