Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2009
    Posts
    1

    Unanswered: Multiple search String for Multiple fields

    Using Ms SQL I have a stored procedure with values

    @search, @cityName, @stateName, @zip

    SELECT DISTINCT
    ct.ID
    ct.Company,
    ct.ContactName,
    cy.CityName,
    st.StateName,
    ct.Zip
    FROM
    Contact AS ct INNER JOIN
    City AS cy ON cy.Id = ct.City INNER JOIN
    State AS st ON st.Id = ct.State
    WHERE
    cy.CityName LIKE COALESCE(@cityName, cy.CityName) OR
    cy.CityName LIKE COALESCE(@search, cy.CityName) AND
    st.StateName IN (@cityName,@search) AND
    ct.Zip = COALESCE(@zip,ct.Zip)

    I want to search using ((@search) OR (@cityName AND @stateName) + @zip)
    JUST NOT HAPPENING

    Hope my question is clear enough..HELP

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Are you passing wildcard characters in with your parameter strings?
    If not, you are effectively performing an "equals" search, not a "like" search.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...and the syntax of your "IN" function is bogus too.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Quote Originally Posted by comwizd
    Using Ms SQL I have a stored procedure with values

    @search, @cityName, @stateName, @zip

    SELECT DISTINCT
    ct.ID
    ct.Company,
    ct.ContactName,
    cy.CityName,
    st.StateName,
    ct.Zip
    FROM
    Contact AS ct INNER JOIN
    City AS cy ON cy.Id = ct.City INNER JOIN
    State AS st ON st.Id = ct.State
    WHERE
    cy.CityName LIKE COALESCE(@cityName, cy.CityName) OR
    cy.CityName LIKE COALESCE(@search, cy.CityName) AND
    st.StateName IN (@cityName,@search) AND
    ct.Zip = COALESCE(@zip,ct.Zip)

    I want to search using ((@search) OR (@cityName AND @stateName) + @zip)
    JUST NOT HAPPENING

    Hope my question is clear enough..HELP
    the way i would do that is

    Code:
    SELECT DISTINCT ct.ID, ct.Company, ct.ContactName, cy.CityName, st.StateName, ct.Zip
    FROM		Contact AS ct
    INNER JOIN	City AS cy
    	ON	cy.Id = ct.City
    INNER JOIN	State AS st
    	ON	st.Id = ct.State
    WHERE		(CityName LIKE @cityName OR @cityName IS null)
    	AND	(StateName LIKE @stateName OR @stateName IS null)
    	AND	(Zip LIKE @zip OR @zip IS null)
    this says find where the city name state name and zip match, but means that if any criteria is null don't match on it. i can't comment on your @search as you don't say what it is

    and like was already said you have to include the wild cards in the param if you want to do a partial match

    i'm not sure what your is doing but its doing nothing like what you say your trying to
    Last edited by m.timoney; 09-30-09 at 11:30.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  5. #5
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    yours if i'm reading your order of president correctly is i think saying

    WHERE
    cy.CityName LIKE COALESCE(@cityName, cy.CityName) OR (
    cy.CityName LIKE COALESCE(@search, cy.CityName) AND
    st.StateName IN (@cityName,@search) AND
    ct.Zip = COALESCE(@zip,ct.Zip))

    or in english

    where @cityname is null or matches the city

    or

    where @searchis null or matches the city and the state name is the city name or @search, and the @zip is null or matches the zip


    personally i always use "or is null" rather than COALESCE as it is easier to understand when written down
    Last edited by m.timoney; 09-30-09 at 11:26.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

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