SQL use comma-separated values with IN clause

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

If you dont want to use dynamic sql, the best way ive found is to create a function which turns a delimited string into a table, something like this works for an Integer list:

CREATE FUNCTION [dbo].[StringToIntList]
(@str VARCHAR (MAX), @delimeter CHAR (1))
RETURNS 
    @result TABLE (
        [ID] INT NULL)
AS
BEGIN

    DECLARE @x XML 
    SET @x = '<t>' + REPLACE(@str, @delimeter, '</t><t>') + '</t>'

    INSERT INTO @result
    SELECT DISTINCT x.i.value('.', 'int') AS token
    FROM @x.nodes('//t') x(i)
    ORDER BY 1

RETURN
END

Then use that in your sp:

CREATE Procedure [dbo].[sp_getUserRoles](
   @pGroupIDs varchar(50)
    )
     As
    BEGIN
        SELECT * FROM CheckList_Groups Where id in (
           SELECT ID FROM dbo.StringToIntList(@pGroupIds,',')
       )
   End

Sure it can't do that,

The generated query would be sth like this

SELECT * FROM CheckList_Groups Where id in ('1,2,3,4')

and sure it can't be executed.

you can build the query in your stored procedure then execute it with exec

'SELECT * FROM CheckList_Groups Where id in (' + @pGroupIDs + ')'

or

SELECT * FROM CheckList_Groups Where charindex(','+id+',' , @pGroupIDs)>0

but you first must add the ',' to start and end of your parameter in your c# code