asp.net - SQL use comma-separated values with IN clause

Question

I am developing an ASP.NET application and passing a string value like "1,2,3,4" into a procedure to select those values which are IN (1,2,3,4) but its saying "Conversion failed when converting the varchar value '1,2,3,4' to data type int."

Here is the aspx code:

private void fillRoles()
{
    /*Read in User Profile Data from database */
    Database db = DatabaseFactory.CreateDatabase();

    DbCommand cmd = db.GetStoredProcCommand("sp_getUserRoles");

    db.AddInParameter(cmd, "@pGroupIDs", System.Data.DbType.String);
    db.SetParameterValue(cmd, "@pGroupIDs", "1,2,3,4");

    IDataReader reader = db.ExecuteReader(cmd);

    DropDownListRole.DataTextField = "Group";
    DropDownListRole.DataValueField = "ID";

    while (reader.Read())
    {
        DropDownListRole.Items.Add((new ListItem(reader[1].ToString(), reader[0].ToString())));
    }

    reader.Close();
}

Here is my procedure:

CREATE Procedure [dbo].[sp_getUserRoles](@pGroupIDs varchar(50))
AS BEGIN
   SELECT * FROM CheckList_Groups Where id in (@pGroupIDs)
END

Answer 1

Here is a workaround I found to do what you are trying to achieve

CREATE Procedure [dbo].[sp_getUserRoles](
   @pGroupIDs varchar(50)
    )
     As
    BEGIN
        SELECT * FROM CheckList_Groups Where (',' + @pGroupIDs +',' LIKE '%,' + CONVERT(VARCHAR, id) + ',%')
   End

This gets your comma delimited list and compares it to the id's(which are represented like so ',1,', ',2,' etc) in the table using LIKE