Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2002
    Posts
    9

    Unanswered: Sending a List to a Stored Procedure

    Hello

    How can I send a list(s) of data into a stored procedure for processing?

    For instance, I have a table: GroupContacts(groupname, userid, contactid).
    At times I will be inserting X amount of records into it, X depends on how many contacts need to be added to a group by a user.

    How can I send a list of (groupname, userid, contactid)'s into a stored procedure and then use some kind of for-loop to iterate through the list and insert the records?

    Thanks
    jenn
    Last edited by jen6677; 02-24-05 at 02:02.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Put the list items into a table. Use the stored procedure to process that table, or a subset of the table.

    -PatP

  3. #3
    Join Date
    Dec 2002
    Posts
    9
    Thanks but,

    I will be calling the stored procedure from an asp.net application so I can't create a table on the fly. I need a stored procedure that will accept a list of entries that will be inserted into an existing table. The list can be of unknown length (ie. each time it could be a different size)

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    So create a table as part of your schema. When you have to support front-end code that behaves that way (needing to pass variable arrays of data), you need to either:

    1) Fix the front-end code
    2) Build an N-tier layer to support the existing front-end, or
    3) Modify your database structure to support it.

    Applications that try to send varying amounts or unstructured data in a single call need some kind of buffer between them and the database. These front ends are usually old code based on a simpler application model (where the tiers aren't well defined, and sometime aren't defined at all). This is often why the old application is being replaced!

    If you get the database right, it is hard to get the application wrong. If you get the database wrong, there is very little chance of getting the application right. The database is actually the key to application performance, if the database interface is clean and quick, the application can scale. When the database needs to do XML-like processing of input, it almost can't be quick and clean.

    -PatP

Posting Permissions

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