Click here to Skip to main content
13,046,176 members (83,646 online)
Click here to Skip to main content
Add your own
alternative version

Stats

21.2K views
8 bookmarked
Posted 1 May 2013

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

, 1 May 2013
Rate this:
Please Sign up or sign in to vote.
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
IDName 
1Carlos Slim
2Bill Gates
3Amancio Ortega
4Warren Buffet
5Larry Ellison
6Charles Koch
7David Koch
8Li Ka-shing
9Liliane Bettencourt
10Bernard 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

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

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
3Amancio Ortega
7David Koch
9Liliane Bettencourt

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

I hope you enjoy this tutorial.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Nassermalek
United States United States
No Biography provided

You may also be interested in...

Pro

Comments and Discussions

 
QuestionThanks for the SplitDelimiterString function Pin
Member 1201190025-Sep-15 8:11
memberMember 1201190025-Sep-15 8:11 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170713.1 | Last Updated 1 May 2013
Article Copyright 2013 by Nassermalek
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid