Query a database from a search with empty values

Question

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?

tvanfosson · Accepted Answer · 2009-01-16 11:49:44Z

4

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)

edited Jan 16, 2009 at 11:49

answered Jan 16, 2009 at 11:41

tvanfosson

534k102 gold badges703 silver badges801 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kristof Over a year ago

when user passes null, null this solution will return all records in the table

balexandre · Accepted Answer · 2009-01-16 11:45:00Z

1

maybe:

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

answered Jan 16, 2009 at 11:45

balexandre

75.5k47 gold badges240 silver badges352 bronze badges

Comments

kristof · Accepted Answer · 2009-01-16 11:49:56Z

0

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

answered Jan 16, 2009 at 11:49

kristof

54k24 gold badges89 silver badges111 bronze badges

Comments

Jonas Lincoln · Accepted Answer · 2009-01-16 12:46:38Z

0

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.

answered Jan 16, 2009 at 12:46

Jonas Lincoln

9,8079 gold badges38 silver badges49 bronze badges

Collectives™ on Stack Overflow

Query a database from a search with empty values

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related