Custom Transpose In SQL Server

When we are playing with the database queries in order to create store procedure, triggers or job...we sometimes need to pass the comma separated string of integer values into the IN clause of SQL query.

Took the example from School where case is that we need to get the list of different Students based on StudentID provided in the procedure as a comma separated string.

DECLARE @StudentIdList AS VARCHAR(8000) = '1,10,15,18,22,45,99'

So when we try to get the Student List based on this parameter using the IN clause as:

SELECT StudentID, StudentName, RollNumber, Age
FROM   Student
WHERE  StudentID IN (@StudentIdList)

What happend!! when we try to run the above query. It will return the error on the IN parameter and mesage is mentioning that Cannot able to convert varchar to bigint....(something like that)

So how do we solve the problem???

Here comes the powerful transpose concept. Here we will create a function which will convert the comma separated string into the table structure where each value will be place into the rows.

CREATE FUNCTION [dbo].[ParamsToTable]
(
    @ParameterString VARCHAR(8000)
)
RETURNS @TblParam TABLE(IdString VARCHAR(10))
AS
BEGIN
    DECLARE @SingleValue VARCHAR(10)

    WHILE LEN(@ParameterString) > 0
    BEGIN
        SET @SingleValue = LEFT(@ParameterString, ISNULL(NULLIF(CHARINDEX(',', @ParameterString) -1, -1), LEN(@ParameterString)))
        SET @ParameterString = SUBSTRING(@ParameterString, ISNULL(NULLIF(CHARINDEX(',', @ParameterString), 0), LEN(@ParameterString)) + 1, LEN(@ParameterString))
     
        INSERT INTO @TblParam VALUES (@SingleValue)
    END
RETURN
END

Use the above created function in your sql query in order to generate the desire result as:

SELECT StudentID, StudentName, RollNumber, Age
FROM   Student
WHERE  StudentID IN (SELECT * FROM ParamsToTable(@StudentIdList))

The reason to put the query block into the function is that by doing this we can use it on several places where we need such type of activity to perform.

Let me know if you have any query over this implementation.

Cheers