Comma separated value parameter in SQL IN clauses

How to use comma separated values parameter in SQL IN Clause in SQL Queries-

The SQL IN clause is very useful, since it allows you to specify exactly which values you want to return.

For this tip,  let's assume we have a database with this table:

CREATE TABLE [dbo].[Employee](
 [ID] [int] NOT NULL,
 [Name] [varchar](50) NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY 

And the Data is:

ID Name
1 Suraj
2 Aden
3 Richerd
4 Shekhar
5 Shankar

We can get the rows from table for whatever Id's we need

SELECT [ID] ,[Name] FROM [dbo].[Employee] WHERE Id IN (1, 3)

And we get the expected data

ID Name
1 Suraj
3 Richerd

But what, if we pass the parameter in string variable

DECLARE @ids VARCHAR(10)
SET @ids = '1,3'
SELECT [ID] ,[Name] FROM [dbo].[Employee] WHERE Id IN (@ids)

You will get the below Error:

Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value '1,3' to data type int.

So, What is the Solution

What we need to do is, convert the comma separated values into a table first. My initial version was inline, and rather messy, so I re-worked it to a user defined function and made it a bit more general purpose. 

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

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

This creates a user defined function that takes a comma separated value string and converts it into a table that SQL does understand - just pass it the sting, and it works it all out.

How to Use the above UDF

Example:

DECLARE @idList VARCHAR(10)
SET @idList = '1,3'
SELECT [ID] ,[Name] FROM [dbo].[Employee] WHERE Id IN (SELECT * FROM dbo.CSVToTable(@idList))

Please Share if you liked this Post.

References: Codeproject