How to pass multiple parameters from a loop to SQL Server Stored Procedure

Question

I have this SQL Server table (sample)

-----------------------------------------
|PartyRef|Level|Description|OtherColumns|
|--------+-----+-----------+------------|
|123ABCDE|3    |CASH ACCT  |OTHER DATA  |
|---------------------------------------|

I use this stored procedure for displaying the data:

ALTER PROCEDURE [dbo].[CRS_AMENDCOUNTERPARTY_VIEW](
    @PARTYREF NVARCHAR(50)=''
) WITH RECOMPILE
AS
BEGIN

CREATE TABLE #TempCRS
(
    p2kValue Varchar(50), 
    ColumnName Varchar(50)
)

INSERT INTO #TempCRS
    SELECT * 
    FROM
        (SELECT
             cast([PartyRef] AS VARCHAR(50)),
             cast([Level] AS VARCHAR(50)),
             cast([Description] AS VARCHAR(50)),
             cast([OtherColumns] AS VARCHAR(50))
         FROM
             [dbo].[tblParty] AS t
         WHERE 
             [PartyRef] = @PARTYREF) AS SourceTable
UNPIVOT
(
    p2kValue FOR ColumnName IN
    (      [PartyRef]
      ,[Level]
      ,[Description]
      ,[OtherColumns] )
) AS unpvt

The output looks like this:

-------------------------
|p2kValue  |ColumnName  |
|----------+------------|
|123ABCDE  |PartyRef    |
|3         |Level       |
|CASH ACCT |Description |
|OTHER DATA|OtherColumns|
-------------------------

Now, for the MVC, I have this model class:

public class ViewPartyModels
{
   public string ColumnName { get; set; }
   public string p2kValue { get; set; }
}

Controller:

public ActionResult Index()
{
    var model = new List<ViewPartyModels>();

    try
    {
        con.Open();

        SqlCommand command = new SqlCommand("[dbo].[AMENDPARTY_VIEW]", con);
        command.CommandType = CommandType.StoredProcedure;

        command.Parameters.AddWithValue("@PARTYREF", Request.Params["Party"]);

        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                model.Add(new ViewCounterpartyDataModels()
                {
                    ColumnName = reader["ColumnName"].ToString(),
                    p2kValue = reader["p2kValue"].ToString()
                });
            }
        }

        con.Close();
    }
    catch (Exception ex)
    {
        throw ex;
    }

    return View(model);
}

[HttpPost]
public ActionResult Update(ViewPartyModels obj, string Update)
{
    if (Request.Form["Update"] != null)
    {
        con.Open();

        SqlCommand command = new SqlCommand("[dbo].[AMENDPARTY_UPDATE]", con);
        command.CommandType = CommandType.StoredProcedure;

        //command.Parameters.AddWithValue(TO BE SOLVED);

        command.ExecuteReader();
        con.Close();
    }

    return View();
}

and View:

@using (Html.BeginForm("Update", "AmendParty", FormMethod.Post)){
<table class="table table-hover">
<tbody>
@foreach (var item in Model)
{
<tr>
<td align="right"><b>@item.ColumnName</b></td>
<td colspan="2"><input type="text" value="@item.p2kValue" name="TxtBasicInfo" id="BasicInfoID"></td>
</tr>
<button type="submit" name="btnUpdate">Save</button>
}

With the above code, I have no problem in displaying the data on the MVC form.

My question is how can I pass the textbox values from the loop to the stored procedure for the update function.

In my real program, I have like 30 textboxes in my form and I know it is not efficient to declare 30 variables in the stored procedure and pass the data one by one.

One more thing to consider is how to assign each data to its corresponding column since basically, it is just from one textbox in a loop.

PLEASE, help me with this. Thank you very much in advance.

Answer 1

I'm suggesting two options:

1) If all parameters are strings, concatenate the values with a delimiter (not present in any string), pass it to SP and split it inside SP

2) Otherwise, populate a table and pass it to SP (https://learn.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine)