Converting Comma separated values to Temporary Table in T-SQL

Today, I want to share a technique that we have to use often in SQL while using with .Net. We have to pass a comma separated string into a Stored Procedure and use it as a table (somehow) to manipulate the results.

Since we had to pass the comma separated string to a stored procedure many times. We created a SQL Function for it and then we only had to call it.

Here is how the code for the T-SQL function looks like...

CREATE FUNCTION [dbo].[GetIDsTableFromIDsList]

(

      @IDsList VARCHAR(MAX)

)

RETURNS @IDsTable TABLE ( [ID] INT )

AS

    BEGIN

        DECLARE @ID VARCHAR(10)

        DECLARE @Pos 

        SET @IDsList = LTRIM(RTRIM(@IDsList)) + ','

        SET @Pos = CHARINDEX(',', @IDsList, 1)

        IF REPLACE(@IDsList, ',', '') <> ''

            BEGIN

                WHILE @Pos > 0

                    BEGIN

                        SET @ID = LTRIM(RTRIM(LEFT(@IDsList, @Pos - 1)))

                        IF @ID <> ''

                            BEGIN

                                INSERT  INTO @IDsTable

                                        ( [ID] )

                                VALUES  ( CAST(@ID AS INT) )

                            END

                        SET @IDsList = RIGHT(@IDsList, LEN(@IDsList) - @Pos)

                        SET @Pos = CHARINDEX(',', @IDsList, 1)

                    END

            END  

        RETURN

    END

This function receives a simple comma separated list of IDs and loops over the list and then after inserting all the values into a temporary table. It returns the temporary table