Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    i may be up a creek on this one (SQL Server 2005, ASP.NET)

    I have a search page that allows the user to search be address, with the crucial feature that the address can be parsed. currently, the UI has 3 address fields and the intention is, if the user wants, they can search by entering, in sequence, data on fields 1 - 3. By design, fields 2 and 3 are set to "CONTAINS" and the user can opt to set the first to "Contains" as well. In addition, for almost all other fields on the search page the default behavior is "Starts With" searches.

    SQL-ally, what this means is I need to be able to query the db multiple times on the same column depending on input. Shop-illy, there's likely to be a lot of resistance to doing any of this in dynamic SQL. I'd prefer to do it in stored procs meself.

    But, I don't know if I can! I cannot find a way to do something like "SELECT blah FROM tables WHERE (table.column IN (input%, %input0%, %input1%). IN doesn't want to consider any case where the user might not already know exactly what they're looking for. ding dang-it. Coalesce has been mentioned, but it doesn't seem to work no matter what I do -- perhaps I am just confused on Coalesce.

    I HAVE to be able to search with some amount of granularity on the address column to make the thing very useful, so any assistance would definately be appreciated...

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: i may be up a creek on this one (SQL Server 2005, ASP.NET)

    Sorry for the lack of replies. IN cannot be combined with wildcards. I don't see how COALESCE could be used for your problem.

    I think you'll have to use something like

    SELECT blah FROM tables WHERE table.column LIKE input & '%' OR table.column LIKE '%' & input0 & '%' OR table.column LIKE '%' & input1 & '%'

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: i may be up a creek on this one (SQL Server 2005, ASP.NET)

    (Edited by HansV to make URL clickable - see <!help=19>Help 19<!/help>)

    thanks for the reply. i have ended up using

    EXEC SP_EXECUTESQL @strSQL

    to process dynamic SQL from code built in the web application. there was some discussion on using COALESCE to create dynamic WHERE clauses based on the following article: http://www.sqlteam.com/article/implementin...ic-where-clause

    But I don't see how COALESCE is a solution, either, as I am trying to provide a lot of flexibility in the web form for searches. If IN provided for wildcards that would be a great solution but that's not the case. FWIW, I prefer a dynamic SQL solution as it's easy to edit and maintain but there are issues with security that must be addressed. I need to test the use of SP_EXECUTE to see if it is sufficient, security-wise.

    Anyway, thanks for the reply!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: i may be up a creek on this one (SQL Server 2005, ASP.NET)

    The article you mention uses COALESCE to return all records if a search condition is Null. This is a very useful technique (there are lots of threads in this forum about ways to do this within Access) but it doesn't apply to your problem.

Posting Permissions

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