How to Get Multiple Rows from Database using Comma Separated IDs (Primary Keys in Delimited String)

Level: Intermediate

Knowledge Required:

• T-SQL
• SQL Server Stored Procedure
• SQL Server User Defined Table Functions

Description:
Sometimes it is required that we need to send more than 1 primary keys to the Database and get the Rows from Table. For example, we want to get all the Rows in which Primary Key = 1, 23, 66 and 99. For this purpose we can create a Dynamic SQL Query in our Application and then execute it as,

SELECT * FROM tbl_SomeTable WHERE PrimaryKey IN (1, 23, 66, 99)

But if we have done all our work using Stored Procedures then we need to execute the same stored procedure 4 times.

CREATE PROCEDURE dbo.GetSomeTableRow
    @PrimaryKey int
AS
Begin
    SET NOCOUNT ON;

    SELECT *
    FROM tbl_SomeTable
    WHERE PrimaryKey = @PrimaryKey;
End

Now we want to send the IDs to this Stored Procedure in one go. We can achieve this by creating a Delimited String, e.g.: Dim sIDs As String sIDs = "1, 22, 33, 99" Then we will create a stored procedure which will accept this Delimited String and return rows. But before creating this Stored Procedure we will first create a User Defined Table Function which will accept the Delimited String and Return the Table having 1 int Field/Column. This function will extract out each integer value from String and add it in a Table then return that Table.

Following is the Script of this function:

CREATE FUNCTION [dbo].GetIntTableFromDelimitedString
( 
    @DelimitedString varchar(max), 
    @Delimiter varchar(10)
)
RETURNS
@ReturnTable TABLE(
    IntValue int
)
AS
Begin
    DECLARE @EachItem varchar(255);
    DECLARE @DelimiterPos int;
    DECLARE @DelimiterPosPrv int;

    SET @DelimitedString = @DelimitedString + ',';
    SET @DelimiterPosPrv = 1;
    SET @DelimiterPos = CHARINDEX(@Delimiter, @DelimitedString, 1);

    WHILE @DelimiterPos > 0
    BEGIN
        SET @EachItem = LTRIM(RTRIM(SUBSTRING(@DelimitedString, @DelimiterPosPrv, @DelimiterPos - @DelimiterPosPrv)));
        IF @EachItem <> ''
            INSERT INTO @ReturnTable(IntValue)
                VALUES(CAST(@EachItem AS int));  
        SET @DelimiterPosPrv = @DelimiterPos + 1;
        SET @DelimiterPos = CHARINDEX(@Delimiter, @DelimitedString, @DelimiterPosPrv);
    END

    Return;
End

Now we can change our Stored Procedure as

CREATE PROCEDURE dbo.GetSomeTableRows
    @PrimaryKeys varchar(max)
AS
Begin
    SET NOCOUNT ON;

    SELECT *
    FROM tbl_SomeTable
    WHERE PrimaryKey IN (
                        SELECT IntValue 
                        FROM dbo.GetIntTableFromDelimitedString(@PrimaryKeys, ',')
                        );
End