Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2005
    Location
    Denver, CO
    Posts
    100

    Unanswered: Stored Procedure with Dependent Parameters

    I'm trying to create a stored procedure that returns some data from a county. I need of course to only allow the selection of counties in the selected state. I can specify @state and @county parameters but this depends on a user know the county in the state they've chosen. Is it possible to create a stored procedure where a user can enter a state parameter, which then creates a temporary table or something with the associated counties then executes the rest of the procedure to return the data from that county? Thanks in advance!
    BillS

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Sounds perfectly fine - how have you designed your tables that store state and county information?
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It dunt to me. Why make the user enter the state if they have entered the county?

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Because a state can have many counties...

    I think what the request is a sort of linked list idea - limit the resultset to only those counties that are in the selected state.
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    Because a state can have many counties...
    How true. But my point is the converse - a county can have only one state so why have the user also enter the state?

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ok, well in my head (I'm thinking linked lists/combo boxes here) the user is presented with 2 boxes, one for state and one for county.

    And instead of searching a big list of counties, you can limit the results by chosing a state. In fact, you don't even need to send the counties to the client before a state is selected, which will be even more efficient.

    However, if the user does end up picking a county then yes, the state can be identified from this easily.

    Are you arguing about whether the state needs to be stored against, for example, an employee?

    to cut a long ramble short - I think we're on the same page and agree here.
    George
    Home | Blog

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That is a UI issue George. I thought this was about passing two parameters to a sproc, one of which could be determined by the other.

    I'm not even sure we are reading the same book

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    How is it a UI issue - the proc just controls the data sent to the client which in this case is a list of counties for a selected state; ergo state is the parameter for said sproc.

    The OP mentioned 2 parameters, but I don't think (no offence intended here) that they were sure of what they meant - I mean, using temporary table was mentioned!

    To summarise, both of us have assumed what the OP wants - it'd be courteous of them to come back and better explain their requirement before we waste more time guessing
    George
    Home | Blog

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I think the OP doesn't make sense, and that is why the two of you are answering different questions.
    If it's not practically useful, then it's practically useless.

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

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I may be wrong, but it sounds like the counties are not filtered out by the state specified, which means that there maybe a combo box, and a list control on the UI. But, the list contents are not filtered out based on the state selected. Hence, we have a state, and possibly (with Ctrl+mouse click) a set of counties, that may or may not belong to the selected state. Consequently (as it's usually the case, I talked myself into the right answer) - I'm with pootle and blindman here. It IS a UI issue, and the OP needs to work a little more before coming back here. With multi-select capabilities of UI, county filtering needs to take place based on selection of the state (stored procedure returning all counties based on the state that was selected by the State combo box), then, providing the OP is working with 2k5, - the second parameter of the procedure in question needs to be of XML data type to encapsulate all the counties selected. Inside the procedure, the OP needs to parse the XML value into a resultset, and join it with the rest of the tables that are involved in final data retrieval....Long, huh? But that's the right way of doing it
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  11. #11
    Join Date
    Jun 2005
    Location
    Denver, CO
    Posts
    100
    Thanks for the extended discussion. I'm actually created a web service so I can pull data from an Adobe Flex application. A web service must be tied to a stored procedure. I'm fairly new to this and learning as I go, but it seems like this may require several procedures. I'm able to display the entire data set in Flex but it's not very useful to an end user without parameters.
    BillS

Posting Permissions

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