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 fnCreateInlineTable 
(    
    @Param AS VARCHAR(7999), -- Deliminated string    
    @Deliminator AS VARCHAR(10) -- Deliminator    
)   RETURNS TABLEAS    

    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 fnCreateInlineTable ( 
                                @ProductIds, ',')
                        )   
END

For the above function fnCreateInlineTable 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 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

Tags: ,