Pass a List<string> variable to stored procedure

and here the usage:

CREATE PROCEDURE get_employees_from_ssn_list @ssn_list VARCHAR(2000) 
AS SELECT * 
     FROM tbl_employees 
    WHERE ssn IN (SELECT VALUE FROM dbo.ParmsToList(@ssn_list,',') );

                                              

1:
2:
3:
4:

Another method is to use XML.
You can frame XML in your c# code for the list and send XML as input parameter. Then in the sp you can use @XML.nodes to convert the XML as relational table format and use it.

angellll : which rows should we change if we are using sql server 2005 ?

>Another method is to use XML.
I suggest that method in my article also. however, I doubt that the xml overhead will make it a more efficient solution...

Can I use nvarchar in the script instead of varchar ?

>Can I use nvarchar in the script instead of varchar ?
yes

>angellll : which rows should we change if we are using sql server 2005 ?
you do not necessarily need to change anything, but you can, if the list will be longer than 8000 characters (including the commas).

if that is so, you change
varchar(8000)
into
varchar(max)

and you could change the substring argument from 8000 to a value that is higher than any item value itself, which is unlikely needed, though

Ok I've created a stored procedure named 'test_procedure' that accepts a @ssn_list parameter.
Now I have a private method in my code.
Tell me if I pass the list correctly.

private void myMethod(List<string> listParameter)
{
   SqlCommand command = new SqlCommand("dbo.test_procedure");
   command.CommandType = CommandType.StoredProcedure;
   command.Parameters.Add(listParameter);
}

Thank you for your help!

change:
command.Parameters.Add(listParameter);

into:
command.Parameters.Add(string.Join(",", listParameter.ToArray());

angellll : I tried what you just posted and know I get this error :

The SqlParameterCollection only accepts non-null SqlParameter type objects, not String objects.

command.Parameters.AddWithValue(string.Join(",", listParameter.ToArray());

I am ok now with the code.
One last tip if you have the time :

How to use your function to create a stored procedure that returns a table using this function that I have created :

ex : SELECT *
       FROM dbo.RowSumBasedOnCode('61.90.00')

RowSumBasedOnCode function accepts one parameter so I must loop in the parameter list that I get with your function.

please see the second comment. note that in the usage, I presumed ',' as separator, your ex. shows that you want to use '.' as separator ...

Separator works fine.The above is one entity and the list I use in my program uses ',' I suppose.

so, where is the problem?

Here is the procedure I wrote :

CREATE PROCEDURE dbo.test_procedure
    @ssn_list VARCHAR(2000)

AS

SELECT *
FROM dbo.RowSumBasedOnCode(@ssn_list)

The problem here is that RowSumBasedOnCode accepts only one parameter and I want to use your dbo.ParmsToList function in a way that I will loop through the list so that I can use the function inside the loop.(Maybe with a while command ?)