locked
How to use multiple comma delimited value in single parameter of stored procedure RRS feed

  • Question

  • User812858302 posted

    Hey Guys ,

    please help

    How to use multiple comma delimited value in single parameter of stored procedure

    Friday, August 28, 2015 7:40 AM

Answers

  • User281315223 posted

    You should be able to do something like using one of the techniques mentioned in this blog post depending on what your exact scenario is which relies on writing your own SPLIT function.

    Can you elaborate a bit more on what your stored procedure looks like? Are you trying to populate an IN clause or something else? Would it be an option to simply perform your split and iterate through each (if you were adding records) :

    // Split your comma delimited values
    var splitValues = yourCommaDelimitedString.Split(',');
    
    // Iterate through each value and perform a query using it
    foreach(var splitValue in splitValues)
    {
        // Open a connection
        using(var connection = ...)
        {
            // Build your query / stored procedure
            var query = "..."
            // Instantiate it
            using(var command = ...)
            {
                // Open your connection
                connection.Open();
                // Add your parameters and execute your query
                command.Parameters.AddWithValue("@Parameter",splitValue);
                command.ExecuteNonQuery();
            }
        }
    }

    Sometimes it's much easier to deal with comma-delimited values server-side (if you can) as opposed to trying to perform some of these types of operations in SQL.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, August 28, 2015 8:48 AM

All replies

  • User281315223 posted

    You should be able to do something like using one of the techniques mentioned in this blog post depending on what your exact scenario is which relies on writing your own SPLIT function.

    Can you elaborate a bit more on what your stored procedure looks like? Are you trying to populate an IN clause or something else? Would it be an option to simply perform your split and iterate through each (if you were adding records) :

    // Split your comma delimited values
    var splitValues = yourCommaDelimitedString.Split(',');
    
    // Iterate through each value and perform a query using it
    foreach(var splitValue in splitValues)
    {
        // Open a connection
        using(var connection = ...)
        {
            // Build your query / stored procedure
            var query = "..."
            // Instantiate it
            using(var command = ...)
            {
                // Open your connection
                connection.Open();
                // Add your parameters and execute your query
                command.Parameters.AddWithValue("@Parameter",splitValue);
                command.ExecuteNonQuery();
            }
        }
    }

    Sometimes it's much easier to deal with comma-delimited values server-side (if you can) as opposed to trying to perform some of these types of operations in SQL.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, August 28, 2015 8:48 AM
  • User812858302 posted

    Hi rion ,

    can you explain,

    i have dropdownlist from which i m passing data to parameter for eg.

    cmd.Parameters.AddWithValue("param_ct", ddl_format.SelectedValue);

    so how to use with split

    Thursday, September 3, 2015 6:31 AM
  • User281315223 posted

    Could you post what your existing SQL statement looks like as that would affect how you pass the values in?

    Thursday, September 3, 2015 9:30 AM