Conditional Common Table Expression (CTE) in SQL

Question

Hi

I'm trying to select the hierarchy of a product category tree in SQL.

My code looks as follows. I'm trying to achieve a dynamic sort order, using IF or Case When on the SortOrder parameter.

The commented line should be active if @SortOrder is equal to 'sortorder'. I tried to add If Else statement around it, but I failed...

Can you help?

CREATE PROCEDURE [dbo].[ProductCategory_SelectHierarchy]
    @SortOrder varchar(30)
AS
    SET NOCOUNT ON;

WITH Categories (Id,ParentId,SortOrder,RowOrder) as
(
        SELECT  parentCategory.Id,
                        parentCategory.ParentId,
                        parentCategory.SortOrder,
                        --cast(REPLACE(STR(parentCategory.SortOrder, 8), ' ', '0') as varchar(30)) 'RowOrder'
                        cast(CAST(DATEPART(YEAR, parentCategory.DateCreated) as varchar(4)) + 
                        CAST(DATEPART(MONTH, parentCategory.DateCreated) as varchar(2)) + 
                        CAST(DATEPART(DD, parentCategory.DateCreated) as varchar(2)) + 
                        CAST(DATEPART(HOUR, parentCategory.DateCreated) as varchar(2)) as varchar(50)) 'RowOrder'
        FROM        ProductCategories parentCategory
        WHERE       ParentId = 0

        UNION ALL

        SELECT  childCategories.Id,
                        childCategories.ParentId,
                        childCategories.SortOrder,
                        --cast(Categories.RowOrder + REPLACE(STR(childCategories.SortOrder, 8), ' ', '0') as varchar(30)) 'RowOrder'
                        cast(Categories.RowOrder + '/' + CAST(DATEPART(YEAR, childCategories.DateCreated) as varchar(4)) + 
                        CAST(DATEPART(MONTH, childCategories.DateCreated) as varchar(2)) + 
                        CAST(DATEPART(DD, childCategories.DateCreated) as varchar(2)) + 
                        CAST(DATEPART(HOUR, childCategories.DateCreated) as varchar(2)) as varchar(50)) 'RowOrder'
        FROM        ProductCategories childCategories
        JOIN        Categories
        ON          childCategories.ParentId = Categories.Id
)

SELECT pc.*, Categories.RowOrder 
FROM Categories 
INNER JOIN ProductCategories pc ON pc.Id = Categories.Id
ORDER BY RowOrder

Answer 1

You should be able to sort it like so:

ORDER BY
    CASE
        WHEN @SortOrder = 'date_column' THEN CONVERT(VARCHAR(20), date_column, 120)
        WHEN @SortOrder = 'customer_id' THEN RIGHT(REPLICATE('0', 20) + CAST(customer_id AS VARCHAR(20)), 20)
        WHEN @SortOrder = 'name' THEN name
        ELSE sort_order
    END

The key is getting all of your sortable columns (or expressions) to end up as the same data type.