Results 1 to 6 of 6
  1. #1
    Join Date
    May 2004
    Posts
    3

    Unhappy Unanswered: Stored Procedure

    I have a simple question regarding TSQL and stored procedures.
    Assuming a table with only two columns, say, ID and Phone.
    The user will then key in up to 10 IDs and up to 20 Phone numbers.
    The query is to find all rows in the table that would match any combinations of these IDs and Phone numbers.

    I am not allowed to use Dynamic SQL. Must do with stored procedures.
    Anyone, please help.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Has the instructor limited you to material covered in class, or can you use any solution that you like? This kind of problem would be a good candidate for full-text index searches.

    -PatP

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "The query is to find all rows in the table that would match any combinations of these IDs and Phone numbers."

    Uhmmm...wouldn't all rows already match a combination?

    I don't think you explained you requirements very well, but I suspect you are looking for some implementation of a cross-join.
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    May 2004
    Posts
    3

    Smile rephrase question

    The problem is there is a table with two columns, IDs and PhoneNumbers.
    There are about 1M rows in this table. The users are allowed to key in 10 IDs and 20 PhoneNumbers on a screen. A stored procedure is needed to scan out all rows in the table that would match any possible combinations of IDs and PhoneNumbers that the user had specified.

    Anyone please help.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    It kind of depends on how the users are keying in the data.

    You can run a query that selects:

    Where ID in ('IDList') and PhoneNumber in ('PhoneList')

    ...but this won't be very efficient.

    If you can store the ID list in a temporary table, and the Phone list in another temporary table, you can create a cross-join dataset that includes all the possible combinations:

    select ID, PhoneNumber from IDList, PhoneNumberList

    ...which you can either store in a new (indexed) temporary table or join directly with your master datasetn as a subquery.
    If it's not practically useful, then it's practically useless.

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

  6. #6
    Join Date
    May 2004
    Posts
    3

    Thumbs up Thank You.

    Thank you, blindman.
    I like your solution with temp tables.
    Please let me know if you just happened to come across a better solution.
    But no cursors please, ha ha.

    Thanks a lot.

    Jonah.

Posting Permissions

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