Query a database from a search with empty values

Question

Hi

I am using asp .net web form search in c#, for example with fields: - 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:

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?

Answer 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.

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

Answer 2

maybe:

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

Answer 3

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

Answer 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.