SitePoint Sponsor |
|
User Tag List
Results 1 to 4 of 4
-
May 24, 2004, 12:42 #1
- Join Date
- Oct 2002
- Location
- Greenock, Scotland
- Posts
- 701
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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!
Cheers
Wullie
-
May 24, 2004, 12:49 #2
- Join Date
- Jan 2001
- Location
- buried in the database shell (Washington, DC)
- Posts
- 1,107
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
Matt - Sybase DBA / PHP fanatic
Sybase/MySQL/Oracle | I don't like MySQL
Download Sybase | DBForums.com - for all your RDBMS talk
-
May 27, 2004, 20:34 #3
- Join Date
- May 2004
- Location
- Bowling Green, KY
- Posts
- 3
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
-
May 28, 2004, 01:59 #4
- Join Date
- Oct 2002
- Location
- Greenock, Scotland
- Posts
- 701
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
Bookmarks