Friday, October 25, 2013

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