Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2003
    Posts
    4

    Unanswered: CASE in WHERE clause?

    I would like to select customers from my database depending on some criteria that the users choose - such as area code, company size etc. Since i have about 15 criteria and the users want to be able to choose many criteria for one selection i can't make one procedure for each criteria - i would end up with about a hundred procedures. I want to let the users choose what criteria to search on and have ONE single select statement that return the customers. Something like:

    select name, address, city etc
    from customer
    where if users want to see customers of a certain type then type = @myInParameterForType
    and if users want to see customers from a certain area then area = @myInParameterForArea

    is this possible? sorry if i made you all confused...my english is not perfect! Thanks in advance!

  2. #2
    Join Date
    Apr 2003
    Location
    Israel
    Posts
    81
    Yes, you may use CASE in where
    --
    kukuk

  3. #3
    Join Date
    Mar 2003
    Location
    France
    Posts
    21

    Re: CASE in WHERE clause?

    one easy (but not very performant) way is to build your "where" clause in your application (ie:web form ) and send it to your sp:
    -----------------------------------------------------------------------------
    CREATE PROCEDURE mySP

    (
    @WhereClause varchar(4000)
    )

    AS
    Declare @SQL varchar(6000)

    begin

    set @SQL = 'SELECT field1, field2 ... from myTable where ' + @WhereClause + ' ORDER BY someID'

    end

    EXEC (@SQL)

    RETURN

    GO
    -----------------------------------------------------------------------------

    when you have many optional parameter it is very conveniant

Posting Permissions

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