I'm a bit of a .NET newbie so I'm looking for some general guidance.
I have a GridView that I need to create optional filters for. Basically the Gridview will load all the data on page load. The page will also have an area above the GridView where the user could enter 1 or more of the following filter options.
- My events only (boolean)
- Dates to and from (2 datetime fields)
- Customer name (string value)
- Event Name (string value)
- Event ID (integer)
How would you handle something like this? Some of my users might enter an Event Name only, leaving all other field blank. Some might enter the From and To Dates aloing with a customer name and leave the other fields blank.
I started creating my Stored Procedure SELECT statement and then realized that it is not going to work because of the wide varity of options the user has.
I've hunted the web and found plenty of examples on filtering a single column but nothing like what I'm describing, where some filter options could be blank and some have values.
How would you solve this dilemma?
(I've included my Stored Procedure SELECT statement, though I know it doesn't work)
ALTER PROCEDURE dbo.GetFilteredResultsForGrid
[PROPOSAL], [DATE], [CUSTOMER], [SHOWNAME], [SHOWDATE], [JOB], [SALE_DATE]
T.REV = (SELECT MAX(REV) FROM PROPOSALS O WHERE T.PROPOSAL = O.PROPOSAL)
SHOWDATE BETWEEN @PARAM_FROM AND @PARAM_TO
CUSTOMER LIKE '% @PARAM_CUSTOMER %'
SHOWNAME LIKE '% @PARAM_EVENTNAME %'
JOB LIKE '% @PARAM_JOB %'