Split comma separated IDs to get batch of records through Stored Procedure

I once struck with problem as I have to pass my IDs to store procedure which would be used IN clause in select query. I have hundred of Ids which i have to pass to store procedure. First i make the comma seperate string and pass it to store procedure but instead of getting result, I got an error. 

Let's create a Person table which have an ID and Name column.

CREATE TABLE Person(
ID INT IDENTITY(1,1) NOT NULL,
Name VARCHAR(200) NOT NULL
    CONSTRAINT PK_Person PRIMARY KEY CLUSTERED (ID ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
              ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Insert a few billionaire names into the Person table.

INSERT INTO Person VALUES ('Carlos Slim')
INSERT INTO Person VALUES ('Bill Gates')
INSERT INTO Person VALUES ('Amancio Ortega')
INSERT INTO Person VALUES ('Warren Buffet')
INSERT INTO Person VALUES ('Larry Ellison')
INSERT INTO Person VALUES ('Charles Koch')
INSERT INTO Person VALUES ('David Koch')
INSERT INTO Person VALUES ('Li Ka-shing')
INSERT INTO Person VALUES ('Liliane Bettencourt')
INSERT INTO Person VALUES ('Bernard Arnault')

Let's check what data have our table

SELECT * FROM Person

ID	Name
1	Carlos Slim
2	Bill Gates
3	Amancio Ortega
4	Warren Buffet
5	Larry Ellison
6	Charles Koch
7	David Koch
8	Li Ka-shing
9	Liliane Bettencourt
10	Bernard Arnault

Let's create a store procedure which will take Ids string and return names against those Ids

CREATE PROCEDURE GetPersonsByIds
@Ids VARCHAR(8000)
AS
BEGIN
    SELECT * FROM Person
    WHERE ID IN (@Ids)
END

Now if we want Names against 3,7 and 9 Ids than we have to pass (3,7,9) to store procedure

EXEC GetPersonsByIds '3,7,9'

This will cause following error

Conversion failed when converting the varchar value '3,7,9' to data type int.

Now the problem is how we pass Ids to our store procedure? 

For this we have to create Table Value function which will split our comma separated string into table

Before going into this i would recommend you to read following topics on MSDN

• CHARINDEX
• SUBSTRING 
• LEN

Create function ‘SplitDelimiterString’ which will split string with delimiter.

CREATE FUNCTION SplitDelimiterString (@StringWithDelimiter VARCHAR(8000), @Delimiter VARCHAR(8))

RETURNS @ItemTable TABLE (Item VARCHAR(8000))

AS
BEGIN
    DECLARE @StartingPosition INT;
    DECLARE @ItemInString VARCHAR(8000);

    SELECT @StartingPosition = 1;
    --Return if string is null or empty
    IF LEN(@StringWithDelimiter) = 0 OR @StringWithDelimiter IS NULL RETURN; 
    
    WHILE @StartingPosition > 0
    BEGIN
        --Get starting index of delimiter .. If string
        --doesn't contain any delimiter than it will returl 0 
        SET @StartingPosition = CHARINDEX(@Delimiter,@StringWithDelimiter); 
        
        --Get item from string        
        IF @StartingPosition > 0                
            SET @ItemInString = SUBSTRING(@StringWithDelimiter,0,@StartingPosition)
        ELSE
            SET @ItemInString = @StringWithDelimiter;
        --If item isn't empty than add to return table    
        IF( LEN(@ItemInString) > 0)
            INSERT INTO @ItemTable(Item) VALUES (@ItemInString);            
        
        --Remove inserted item from string
        SET @StringWithDelimiter = SUBSTRING(@StringWithDelimiter,@StartingPosition + 
                     LEN(@Delimiter),LEN(@StringWithDelimiter) - @StartingPosition)
        
        --Break loop if string is empty
        IF LEN(@StringWithDelimiter) = 0 BREAK;
    END
     
    RETURN
END

If we pass comma separated string to this funciton, it will return us table e.g.

SELECT * FROM SplitDelimiterString('3,7,9',',')

OUTPUT:

Item
3
7
9 

So we have to modify our store procedure a little to get expected result

ALTER PROCEDURE GetPersonsByIds
@Ids VARCHAR(8000)
AS
BEGIN
    SELECT * FROM Person
    WHERE ID IN (SELECT * FROM SplitDelimiterString(@Ids, ','))
END

Now pass Ids to store procedure and let see what is the output

EXEC GetPersonsByIds '3,7,9'

Output:

ID	Name
3	Amancio Ortega
7	David Koch
9	Liliane Bettencourt

Bingo! We pass the Ids string to store procedure and get all the names against those ids.

I hope you enjoy this tutorial.