Sometimes you may want to pass a comma separated string as an argument to stored procedure or SQL function and parse within the procedure. This sample code shows how to parse a string based any delimiter you specify.

CREATE FUNCTION dbo.CreateInlineTable (
    @Param AS VARCHAR(7999), -- Deliminated string
    @Deliminator AS VARCHAR(10) -- Deliminator
    )   RETURNS TABLE
AS
    RETURN ( SELECT Substring(@Deliminator + @Param + @Deliminator, Number + LEN(@Deliminator), 
                    CHARINDEX(@Deliminator, @Deliminator + @Param + @Deliminator, Number + LEN(@Deliminator)) - Number - LEN(@Deliminator) ) AS PARAM
               FROM InlineNumbers Numbers
              WHERE Number <= LEN(@Deliminator + @Param + @Deliminator) - LEN(@Deliminator)
                    AND Substring(@Deliminator + @Param + @Deliminator, Number,LEN(@Deliminator)) = @Deliminator)
 
GO

Imagine you have a user interface with a list of products. On the side of each product you have a delete checkbox and then a delete button below the list. Even though a user can delete multiple products at once, you only want to query the database once. One way to achieve this is by sending a comma separated string with product id’s to a stored procedure. The stored procedure will then separate the product ids using the above function and return a table.

CREATE PROCEDURE DeteteProductSP
(
    @ProductIds    VARCHAR(800)
)
AS  
BEGIN  
 
    DELETE 
      FROM Products
     WHERE ProductId IN ( SELECT PARAM 
                            FROM dbo.CreateInlineTable ( @ProductIds, ',')
                        )
   
END

For the above function dbo.CreateInlineTable to separate the values, we need to create another table in our database, that returns the Substring position numbers, this table needs to be populated from 1 to 8000

CREATE TABLE dbo.InlineNumbers
(    
    Number                INT                    NOT NULL    
)
GO
 
--Populate this table with number from 1 to 8000
DECLARE @Key AS INT 
 
SET @Key = 1 
WHILE @Key < 8001 
BEGIN
 
    INSERT INTO InlineNumbers ( Number ) 
        VALUES ( @Key ) 
        
    SET @Key = @Key + 1
END