SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Guru Wullie's Avatar
    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
    ASP.NET Freelance Web Developer
    Bored? Check out my photography folio at Flickr

  2. #2
    Database Jedi MattR's Avatar
    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.

  3. #3
    SitePoint Member emmasmygirl's Avatar
    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

  4. #4
    SitePoint Guru Wullie's Avatar
    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
    ASP.NET Freelance Web Developer
    Bored? Check out my photography folio at Flickr


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •