Why does only the last item of comma-delimited list get processed by stored proc when called from C# web form?

Question

I am using C# in Visual Studio 2013 and SQL Server 2012.

When the user of my ASP.NET web form enters a newline-separated list of codes and clicks submit, the code behind should read the values, concatenate them into a comma-delimited string, and pass the string to a method that calls a stored proc. The stored proc parses out the values and sets the active field to 1 on each record with a matching code.

The Product table is defined as:

id (PK, int, not null),
name (varchar(20), not null),
code (varchar(20), null),
active (bit, not null)

The Product table contains the following five records:

id  name        code    active
--  ----        ----    ------
1   Product 1   AAA     0
2   Product 2   BBB     0
3   Product 3   CCC     0
4   Product 4   DDD     0
5   Product 5   EEE     0

I created the following stored proc:

CREATE PROCEDURE [dbo].[MarkListAsActive] 
    @codeList   varchar(MAX)
AS
UPDATE
    dbo.Product
SET 
    active = 1
WHERE
    code IN (SELECT val FROM dbo.f_split(@codeList, ','))

dbo.f_split handles parsing the comma-delimited string. I copied it from this post: https://stackoverflow.com/a/17481595/2677169

If I execute the stored proc in SQL Server Management Studio, all five records get updated (as expected).

DECLARE @return_value int

EXEC    @return_value = [dbo].[MarkListAsActive]
        @codeList = N'AAA,BBB,CCC,DDD,EEE'

SELECT  'Return Value' = @return_value
GO

However, if I call the stored proc from the code behind of my .aspx page, only the last item in the list gets marked as active.

protected void SubmitButton_Click(object sender, EventArgs e)
{
    string[] codeArray;
    char separator = '\n';
    OutputLabel.Text = "";
    codeArray = ProductCodeTextBox.Text.Split(separator);

    OutputLabel.Text += "The products with the following codes were marked as active:<br />";

    string codes = "";

    // TODO: Replace with regex that changes newlines to commas
    for (int i = 0; i < codeArray.Length; i++)
    {
        codes += codeArray[i] + ",";
        OutputLabel.Text += codeArray[i] + "<br />";
    }
    codes = codes.Substring(0, codes.Length - 1);
    Utilities.Log(codes);
    DataAccess.MarkListAsActive(codes);
}

public static void MarkListAsActive(string codeList)
{
    try
    {
        string connectionString = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;

        using (SqlConnection conn = new SqlConnection(connectionString))
        using (SqlCommand command = new SqlCommand("[dbo].[MarkListAsActive]", conn)
        {
            CommandType = CommandType.StoredProcedure
        })
        {
            conn.Open();
            command.Parameters.Add("@codeList", codeList);
            command.ExecuteNonQuery();
            conn.Close();
        }
    }
    catch (Exception ex)
    {
        Utilities.Log(String.Format("Error in MarkListAsActive: {0}\n{1}", ex.Message, ex.StackTrace));
    }
    return;
}

Note that I verified that the string being passed to MarkListAsActive() is correct.

Another approach: I tried looping through the codeArray and calling MarkListAsActive() for each item. Even this brute force (and inefficient) approach only updated the last item in the array.

Yet another aproach: I also tried a table valued parameter, but it too only updated the record corresponding to the last item in the input.

Thank you in advance for your help.

Answer 1

Your where clause is incorrect. You cannot do

... code in (select val ...

You need to join the result from the f_split function to the table.

For example:

UPDATE p
SET p.active = 1
FROM dbo.Product p inner join dbo.f_split(@codeList, ',') f
WHERE p.code = f.val;

Answer 2

Change the IN clause to (SELECT val FROM dbo.f_split(@codeList, ',') where val is not null).

If that doesn't work follow these steps:
(You should know how to debug something like this anyway.)

First, use SQL Profiler to see what exactly is submitted to the database. See here for how to do that - its not as bad as it looks and is invaluable. If not what is expected, you have a problem on the front end.

Second, if the call to the database looks good then take the parameter you see in SQL Profiler and execute dbo.f_split() using it to see if you get back what you think you should.

Last, change your UPDATE statement into a SELECT statement and run it with what you did in the second step and see if you get back something that looks correct.

One of those steps will not return what is expected and that will lead you to the problem.