Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    124

    Unanswered: comma delimited list update stored procedure

    I have a stored procedure that I want to use to update multiple records. I'm using ASP and the request form collection is returning values in a comma delimited list.
    Example:
    name1 = value1, value2, value3, etc.
    name2 = value1, value2, value3, etc.
    name3 = value1, value2, value3, etc.

    Here is how I wrote my stored procedure:

    CREATE PROCEDURE dbo.Sp_Update_ABR_Record
    (
    @abrID int,
    @ddo varchar(50),
    @ay varchar(50),
    @strategy varchar(10),
    @budgacct varchar(10),
    @budgobj varchar(10),
    @origamt
    varchar(50),
    @incrdecr varchar(50),
    @review char(10),
    @abrdetlsID varchar(50)
    )
    AS
    UPDATE DIM_ABR_REQ_HDR
    SET ABR_review = @review
    WHERE ABR_ID = @abrID

    UPDATE DIM_ABR_REQ_DETLS
    SET ABR_DETLS_DDO = @ddo, ABR_DETLS_AY = @ay,
    ABR_DETLS_STRATEGY = @strategy, ABR_DETLS_BUDG_ACCT = @budgacct,
    ABR_DETLS_BUDG_OBJ = @budgobj, ABR_DETLS_FUND_ORIG_AMT = convert(money, @origamt), ABR_DETLS_FUND_INCR_DECR = convert(money, @incrdecr)
    WHERE
    ABR_DETLS_ID = @abrdetlsID
    GO

    The second update is where the comma delimited list needs to be handled. The first update is only updating one field once.

    Is there a way to write the procedure to handle the comma delimited list? Or, is the way I have the stored procedure okay and I just need to handle the comma delimited list within the ASP code? I'm not sure which way I can accomplish this?

    Thanks for any help.
    -D-

  2. #2
    Join Date
    Sep 2004
    Posts
    15
    Hi,
    I think providing values in XML format rather than comma delimited will deliver more flexibility to retrieve values from that.
    Still you can write User Defined Functions to pass the comma delimited string into that and get particular value.
    Regards,
    Leila

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Which parameter is the list, and how did you intend to use it?

    -PatP

  4. #4
    Join Date
    Jan 2004
    Posts
    124
    The request form collection will return each of these parameters in a comma delimited list, which the second update in the stored procedure would handle:

    @ddo varchar(50),
    @ay varchar(50),
    @strategy varchar(10),
    @budgacct varchar(10),
    @budgobj varchar(10),
    @origamt varchar(50),
    @incrdecr varchar(50),
    @abrdetlsID varchar(50)

    So, should I use the split function and pass the information into the procedure that way? I've used the split funciton for one parameter, but not multiple parameters. So, I wasn't sure how to code for that?

    Thank you for your help.
    -D-

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    If the number of parameters the same across the set then do parse them and execute the procedure once for each combination. Otherwise, you need to review this design and get away from doing thing this way.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Jan 2004
    Posts
    124
    Yes, there are the same number of parameters for each variable in the set. So, if I use the split function:

    ddolist = Split(Request.Form("ddo"),", "
    strategylist = Split(Request.Form("strategy"),", "
    aylist = Split(Request.Form("ay"),", "
    budgobjlist = Split(Request.Form("budgobj"),", "
    budgacctlist = Split(Request.Form("budgacct"),", "
    incrdecrlist = Split(Request.Form("incrdecr"),", "
    abrdetlsIDlist = Split(Request.Form("abrdetlsID"),", "

    I can use any of the parameters to determine the number of loops by using Ubound?

    i.e.:

    Loop_Max = UBound(abrdetlsIDlist)
    For x = 0 to Loop_Max
    Command_Name.Parameters.Item("@ddo").Value = ddolist(x)
    Command_Name.Parameters.Item("@strategy").Value = strategylist(x)
    Command_Name.Parameters.Item("@ay").Value = aylist(x)
    Command_Name.Parameters.Item("@budgobj").Value = budgobjlist(x)
    Command_Name.Parameters.Item("@budgacct").Value = budgacctlist(x)
    Command_Name.Parameters.Item("@incrdecr").Value = incrdecrlist(x)
    Command_Name.Parameters.Item("@abrdetlsID").Value = abrdetlsIDlist(x)
    Command_Name.Execute()
    Next

    Would that be correct?

    Thank you for your help.
    Regards,
    -D-

Posting Permissions

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