Multiple Search Words for SQL

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • Keith

    Multiple Search Words for SQL

    I have a small form which searches a SQL DB on several fields - ie.

    WHERE Field1 = xxx AND Field2 = xxx AND Field3 = xxx

    How can I do this so that if one search criteria is left blank, it ignores
    it instead of trying to match it in the DB?


  • Curt_C [MVP]

    #2
    Re: Multiple Search Words for SQL

    lots of IF statements, or write a function to wrap around each before
    appending to the SQL string

    --
    Curt Christianson
    Owner/Lead Developer, DF-Software
    Site: http://www.Darkfalz.com
    Blog: http://blog.Darkfalz.com


    "Keith" <@.> wrote in message news:uwYHo9hUEH A.2408@tk2msftn gp13.phx.gbl...[color=blue]
    > I have a small form which searches a SQL DB on several fields - ie.
    >
    > WHERE Field1 = xxx AND Field2 = xxx AND Field3 = xxx
    >
    > How can I do this so that if one search criteria is left blank, it ignores
    > it instead of trying to match it in the DB?
    >
    >[/color]


    Comment

    • David Copenhaver

      #3
      Re: Multiple Search Words for SQL

      you can wrap each field in a null check

      WHERE Field1 = xxx AND Field2 = xxx AND Field3 = xxx

      can be

      Where (Field1 = xxx or isnull(field1,t rue)) and (Field2 = xxx or
      isnull(field2,t rue)) ....



      *** Sent via Devdex http://www.devdex.com ***
      Don't just participate in USENET...get rewarded for it!

      Comment

      • Aaron [SQL Server MVP]

        #4
        Re: Multiple Search Words for SQL

        You could say:

        WHERE (@param1 IS NULL OR column1 = @param1)
        or
        WHERE column1 = COALESCE(@param 1, column1)

        Neither of these will use indexes, so performance might not be optimal.

        If you have a small number of options, you could use IF statements to choose
        a static SELECT statement, e.g.

        IF @param1 IS NOT NULL AND @param2 IS NOT NULL
        SELECT * FROM tbl WHERE column1 = @param1 AND column2 = @param2

        IF @param1 IS NULL AND @param2 IS NOT NULL
        SELECT * FROM tbl WHERE column2 = @param2

        etc etc

        If the number of options is large, you could try dynamic SQL (either a
        stored procedure that builds the statement and executes it, or having ASP
        build the query and send it as adCmdText).

        --
        Please contact this domain's administrator as their DNS Made Easy services have expired.

        (Reverse address to reply.)




        "Keith" <@.> wrote in message news:uwYHo9hUEH A.2408@tk2msftn gp13.phx.gbl...[color=blue]
        > I have a small form which searches a SQL DB on several fields - ie.
        >
        > WHERE Field1 = xxx AND Field2 = xxx AND Field3 = xxx
        >
        > How can I do this so that if one search criteria is left blank, it ignores
        > it instead of trying to match it in the DB?
        >
        >[/color]


        Comment

        • Aaron [SQL Server MVP]

          #5
          Re: Multiple Search Words for SQL

          > Where (Field1 = xxx or isnull(field1,t rue)) and (Field2 = xxx or[color=blue]
          > isnull(field2,t rue)) ....[/color]

          IsNull() doesn't work this way in SQL Server!


          Comment

          • David Copenhaver

            #6
            Re: Multiple Search Words for SQL

            Woops!
            I had Oracle on the Brain.
            You are absolutly right Aaron.
            in SQL server it would be
            (Field1 = xxx or field1 is null)

            DRC
            Alphabet collector (MCP,MCSD,MCSA, NECD ECT.)

            *** Sent via Devdex http://www.devdex.com ***
            Don't just participate in USENET...get rewarded for it!

            Comment

            • Bullschmidt

              #7
              Re: Multiple Search Words for SQL

              And here's a related article I put together that hopefully might give
              you some more ideas too:

              ASP Design Tips - Search For an Expression on Multiple Fields


              Best regards,
              J. Paul Schmidt, Freelance ASP Web Consultant
              Do you have an idea for a database on the Web? Hi! I'm a freelance Web and/or database developer who can make database information available 'live' on the Web.

              ASP Design Tips, ASP Web Database Demo, Free ASP Bar Chart Tool...


              *** Sent via Devdex http://www.devdex.com ***
              Don't just participate in USENET...get rewarded for it!

              Comment

              Working...