Good afternoon,

I am trying to do a dynamic Order By, I have verified the query and I cannot find the fault.

But for some reason it is not ordering me with the CASE commands.

Has anyone tried to do a dynamic order by or find an error in my query?

SELECT 
     {SaleDetail}.[ProductId] AS 'ProductId', SUM({SaleDetail}.[Count]), SUM({SaleDetail}.[Gain]), SUM({SaleDetail}.[Cost]), {Product}.[Name]
From
    {Sale} INNER JOIN {SaleDetail}
            ON {Sale}.[Id] = {SaleDetail}.[SaleId]
        INNER JOIN {Product}
            On {SaleDetail}.[ProductId] = {Product}.[Id]
WHERE
        {Sale}.[Date] >= (CASE WHEN @DateTimeIni = '1900-01-01 00:00:00' THEN {Sale}.[Date] ELSE @DateTimeIni END) AND 
        {Sale}.[Date] <= (CASE WHEN @DateTimeEnd = '1900-01-01 00:00:00' THEN {Sale}.[Date] ELSE @DateTimeEnd END) AND
        -- Obligatorio
        {Sale}.[Is_Active] = 1 AND
        {SaleDetail}.[Is_Active] = 1
        
GROUP BY {SaleDetail}.[ProductId], {Product}.[Name]

ORDER BY (CASE @OrderByQuantityGainSale 
                WHEN 'Quantity' THEN 2
                WHEN 'Gain' THEN 3
                WHEN 'Sale' THEN 4
                ELSE 1
            END) DESC

By default it should be

Order by 1 Desc