SQL Server - How to include a WHERE dependant on choice
I have a form submittion which goes to a page that performs a search on my database using a stored procedure. The search page has these 3 options.
Search for Event Type <input>
on date <input>
in area <input>
The stored procedure looks much like the following
SELECT * FROM tblEvents WHERE eventID = @iEventType AND date = dtEventStartDate and areaID = iEventAreaID
How can I make it that if 'event type' or 'area' are not input in the search that the 'WHERE eventID = @iEventType' or the 'WHERE areaID = iEventAreaID' is not included in the procedure.
From looking around I think i use t-SQL. If this is the case could anyone point me to a tutorial that will show me how I can do this.
BIG thanks to anyone who can help me out!
Presuming you're using Microsoft's SQL Server, take a look at the 'EXEC( )' function. It lets you execute arbitrary SQL code (with some limitations) from within a stored procedure.
This is another option. If these fields are not filled in, selected, etc., pass in a NULL in place of their value. You can then use this code.
SELECT * FROM tblEvents
WHERE date = dtEventStartDate
AND (eventID = @iEventType OR @iEventType IS NULL)
AND (areaID = @iEventAreaID OR @iEventAreadID IS NULL)
Hope this helps
Thanks for your reply's.
The option I went for was close to yours emmasmygirl. I used the function COALESCE which returns teh first NOT NULL value. I simply passed the parameter and referenced the current field so that should no value have been supplied it will match the where to the field in question.
WHERE dbo.tblAreas.areaID = COALESCE(@EventTypeID, dbo.tblAreas.areaID)
It's a pretty nifty function, if you've never used it before you should give it a look.