How you write a StoreProc with "Where in" clause

This is usual store procedure we will write to get particular userid by passing single Userid as parameter

create Procedure [dbo].[procName]
(@Userid int)
AS select * from users where UserId =@Userid

If you want to pass a List of values to fetch the userId's
like
select * from users UserId in (101,102,103);

How we will pass these list of values in storeProc?

Modify the StoreProcedure like this

Create Procedure [dbo].[procName]
(@Userid varchar(500))

AS

BEGIN
SET NOCOUNT ON

DECLARE @SQL varchar(600)

SET @SQL =

'select  * from users
where UserId IN (' + @Userid + ')'

EXEC(@SQL)
END

• Now pass the input parameter as Varchar @Userid varchar(500)
• DECLARE @SQL varchar(600)
  
• Now Use the @SQL as a String so the Value of the string will be 'select * from users where UserId IN ('+ @Userid + ')'
• Now Execute sql by EXEC(@SQL)

Now in Your C# Code you can get the List values and convert to values with comma seperated

  List<int> someVal = new List<int>()
            {
                101,102,103,104,105
            };
         
            string Val="";
            string finalVal;
            foreach (var i in someVal)
            {
                Val = Val+i + ",";
            }
            finalVal = Val.TrimEnd(',');

DataTable dt=GetUserData(finalVal );

public DataTable GetUserData(string userID)
    {
        SqlConnection conn = null;
        SqlDataReader rdr = null;
        DataTable dt1 = new DataTable();

        // create and open a connection object
        conn = new
            SqlConnection("Server=(local);DataBase=Northwind;Integrated Security=SSPI");
        conn.Open();

        // 1.  create a command object identifying
        //     the stored procedure
        SqlCommand cmd = new SqlCommand("procName", conn);

        // 2. set the command object so it knows
        //    to execute a stored procedure
        cmd.CommandType = CommandType.StoredProcedure;

        // 3. add parameter to command, which
        //    will be passed to the stored procedure
        cmd.Parameters.Add(
            new SqlParameter("@Userid", Userid));

        // execute the command
        rdr = cmd.ExecuteReader();

        dt1.Load(rdr);

        return dt1;

    }