SQL use comma-separated values with IN clause

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

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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

Method 2

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

Method 3

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

Method 4

It is not possible to put those values (the comma separated string) in a parameter-value.

What you’ll have to do, is to create the SQL Statement in your stored procedure dynamically, by string concatenation. You’ll have to execute it with the sp_executesql stored procedure then.

 CREATE PROCEDURE [dbo].[getUserRoles]( @groupIds NVARCHAR(50) ) 
 AS BEGIN   
    DECLARE @statement NVARCHAR(255)

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

    execute sp_executesql @statement 
 END

Also, not that I named the SP getUserRoles instead of sp_getUserRoles.
The reason is very simple: when you execute a stored procedure whose name starts with sp_, then SQL Server will first query the master database to find that stored procedure, which causes a performance hit offcourse.

Method 5

The way you are trying to do this is slightly wrong. You will need to use EXECUTE in order to achieve this.

CREATE PROCEDURE [dbo].[sp_getUserRoles](@pGroupIDs nvarchar(50))
As
BEGIN         
    EXECUTE (N'SELECT * FROM CheckList_Groups Where id in (' + @pGroupIDs + ')';
END

Method 6

DECLARE @TagId  NVARCHAR(100)  = '43,81'

SELECT * FROM TABLE WHERE TagId IN (SELECT TRIM(VALUE) FROM  STRING_SPLIT( @TagId , ',' )  )

USE STRING_SPLIT FUNCTION FOR THIS

Method 7

You need to use SP_executesql to achieve this functionllity

CREATE Procedure [dbo].[sp_getUserRoles](
   @pGroupIDs varchar(50)
    )
     As
    BEGIN

EXECUTE sp_executesql 
          N'SELECT * FROM CheckList_Groups Where id in (@pGroupIDs)',
          N'@level varchar(50)',
          @level = @pGroupIDs;

 End

Method 8

The IN clause can’t take a bound parameter like that. What it’s being given when the query is actually created is SELECT * FROM CheckList_Groups Where id in ('1,2,3,4'). Essentially the IN clause is being passed a single string.

Method 9

First create function –

Just run this code

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[CSVToTable] (@InStr VARCHAR(MAX))
RETURNS @TempTab TABLE
   (id int not null)
AS
BEGIN
    ;-- Ensure input ends with comma
    SET @InStr = REPLACE(@InStr + ',', ',,', ',')
    DECLARE @SP INT
DECLARE @VALUE VARCHAR(1000)
WHILE PATINDEX('%,%', @INSTR ) <> 0 
BEGIN
   SELECT  @SP = PATINDEX('%,%',@INSTR)
   SELECT  @VALUE = LEFT(@INSTR , @SP - 1)
   SELECT  @INSTR = STUFF(@INSTR, 1, @SP, '')
   INSERT INTO @TempTab(id) VALUES (@VALUE)
END
    RETURN
END
GO

Then –

Use function in bracket with select statment –

DECLARE @LIST VARCHAR(200)
SET @LIST = '1,3'
SELECT Id, Descr FROM CSVDemo WHERE Id IN (SELECT * FROM dbo.CSVToTable(@LIST))

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0