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

Recursion In LINQ

A method which called itself is called "Recursive". These methods are extensively used in daily programming practices. We use it to solve the complex problems or puzzles we face in code. Using recursion add needless complication in other programs.

Before jump into the LINQ world, let me tell you the method definition and implementation. The method should contain the reference variable parameter. It checks a condition near the top of its method body, as many recursive algorithms do.

So if you see the same method signature repeated many times in the call stack, you have a recursive method.

Back to topic, so how the we can acheive the recursion in LINQ query. It is good here to follow the example and write a LINQ query according to it.

Suppose I have list Candy. And each candy have different flavors while some flavor may extend to their child flavors. We need to calculate the 'Total Count' of candy flavors. How do we acheive this?

Diagram of this case is like this tree:

Candy --> Flavor1
Candy --> Flavor2 --> SubFlavor1 --> One, Two
Candy --> Flavor2 --> 
Candy --> Flavor2 --> SubFlavor1 --> Three --> Four, Six

SOLUTION:

We will use 'SelectMany' in order to calculate the 'Total Flavors' count. When we use 'SelectMany', it collapses many elements into a single collection. The code logic will be:

var flavorCount = candyList.SelectMany(sm => sm.Flavors).Count(); // C#

Dim flavorCount = candyList.SelectMany(Function(sm) sm.Flavors).Count() ' VB.NET

You see that!!! this is very simple in LINQ in order to count the 'Total Flavor'. Here I used 'SelectMany' in order to get the count. 

Let me know if have any queries left.

Cheers