SQL Server - How to include a WHERE dependant on choice

[Wullie]

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!

Cheers

Wullie

[MattR]

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.

[emmasmygirl]

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

[Wullie]

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.

Cheers

Wullie