Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2004
    Posts
    124

    Unanswered: sql search statement

    I'm searching a table based on 4 possible search criteria...city, name, date and type. The search can use just one of the search criteria or any combination of the four...i.e. city and name or city and type or name, date and type, etc.

    The statement for one search criteria could be written like the following:

    Select *
    From dbo.myTable
    Where field = 'myvar'

    myvar = Request.Form("fieldname")

    What would be the way to construct the statement to allow searching for any combination of the 4 search criteria?

    Thanks in advance,
    -Dman100-

  2. #2
    Join Date
    Dec 2004
    Posts
    5
    SELECT * from table
    where
    field1 = "somevalue"
    OR
    field2 = "somevalue2"
    OR
    field3 = "somevalue3"

    Hope this is what you were looking for.

  3. #3
    Join Date
    Dec 2004
    Posts
    5
    I just reread your post and noticed I didn't supply the correct answer.

    Judging by your post, you are using ASP to generate the query string anyway. Why not setup an if/then to add the additional critera to your query?

    Otherwise, you probably need to use a stored procedure taking the 4 fields as values and checking them on the server end for null values, I'd suggest the ASP option (but I come from a procedural programming language background, so I like doing things "logically" and not using all these crazy "one line of code does 90 things" statements).

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662

    ...to ensure (clustered) index seek...

    It has to be a procedure, otherwise the level of convolution will be exponentially increasing on the front-end:

    create proc dbo.sp_search (
    @city varchar(100) = null,
    @name varchar(100) = null,
    @date datetime = null,
    @type char(1) /*replace with correct datatype*/ = null)
    as
    declare @tbl table (
    city varchar(100),
    name varchar(100),
    date datetime,
    type char(1) /*replace with correct datatype*/ )
    insert @tbl select @city, @name, @date, @type
    select t.* from dbo.Table t
    inner join @tbl t1 on (
    t.city = isnull(t1.city, t.city) and
    t.name = isnull(t1.name, t.name) and
    t.date = isnull(t1.date, t.date) and
    t.type = isnull(t1.type, t.type)
    )

    The only problem I see is if the physical table contains null values for any of the searched fields. Then you'll have to check for null values in the table fields which will eliminate the possibility of index usage and revert evertyhing to table scan.

    EDITED:

    I usually create computed columns and perform search on them. The base formula can eliminate the nulls and replace them with some neutral value which then can be used in direct comparison operation using the template above.
    Last edited by rdjabarov; 12-12-04 at 22:06. Reason: how to avoid table scan...
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    construct the query as follows (yes, it's dynamic, but dynamic queries are only marginally slower than compiled queries, and the convolution to achieve a compilable query in the stored proc is horrendous)

    here the scripting language is in blue, the sql is in black, and the scripting language checks for the presence of values in the front-end form fields --
    Code:
    select foo
         , bar
      from yourtable
     where 1=1
    if field1 value is provided
       and column1 = value1
    endif
    if field2 value is provided
       and column2 = value2
    endif
    if field3 value is provided
       and column3 = value3
    endif
    if field4 value is provided
       and column4 = value4
    endif
    so basically this is what wahrheit (fabulous name, by the way) was suggesting

    it works for any of the 22 different combinations of 4 values being provided, including if none is provided, when 1=1 ensures that all rows are returned
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2004
    Posts
    124
    Thanks for the help...

    I tried the following query, but the I'm getting a syntax error:

    SELECT *
    FROM dbo.DIM_ABR_MASTER_HDR
    WHERE 1=1
    if varddo <> " "
    AND ABR_ddo = varddo
    end if
    if varreqname <> " "
    AND ABR_requestor = varreqname
    end if
    if vardt <> " "
    AND ABR_dt = vardt
    end if
    if vartype <> " "
    AND ABR_type = vartype
    end if

    Is the query written incorrectly? Thanks.
    -Dman100-

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, you need to distinguish between asp and sql

    asp builds the sql string, then executes it

    i can't help you, however, because i don't do asp
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jan 2004
    Posts
    124
    Okay, I got it...I think this is working correctly...

    Dim rsAlloSearch
    Dim rsAlloSearch_numRows

    Set rsAlloSearch = Server.CreateObject("ADODB.Recordset")
    rsAlloSearch.ActiveConnection = MM_DBConn_STRING
    rsAlloSearch.Source = "SELECT * FROM dbo.DIM_ABR_MASTER_HDR WHERE 1=1"
    rsAlloSearch.CursorType = 0
    rsAlloSearch.CursorLocation = 2
    rsAlloSearch.LockType = 1
    rsAlloSearch.Open()

    rsAlloSearch_numRows = 0

    if Session("ddo") <> "" and rsAlloSearch.Fields.Item("ABR_ddo").Value = Session("ddo") then
    end if
    if Session("reqname") <> "" and rsAlloSearch.Fields.Item("ABR_requestor").Value = Session("reqname") then
    end if
    if Session("date") <> "" and rsAlloSearch.Fields.Item("ABR_dt").Value = Session("date") then
    end if
    if Session("Alloabr") <> "" and rsAlloSearch.Fields.Item("ABR_type").Value = Session("Alloabr") then
    end if

    Is this what you were you referring to?
    Thanks,
    -Dman100-

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i have no idea, i don't do asp
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Even though Rudy doesn't do ASP, so he can't tell you, that is exactly what he meant!

    -PatP

Posting Permissions

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