How to use Order By in a stored procedure without using dynamic SQL

Question

I've the following MS SQL stored procedure. I need to sort the results without using dynamic SQL and sp_executesql method

@Order by can have the possible values ProductName ASC, ProductName DESC, ProductCode ASC, VendorName DESC, VendorCode or ClientName

I was trying to use ORDER BY CASE, is there any issue if the ProductName, ProductCode are of different type?

ALTER PROCEDURE [dbo].[SortedReport]
(
    @ClientID INT,
    @RecordLimit,
    @FromDate DATETIME,
    @ToDate DATETIME,
    @OrderBy NVARCHAR(MAX)
)

AS
BEGIN

IF (@OrderBy IS NULL) BEGIN
    SET @OrderBy = 'ProductName';
END    

SELECT TOP (@RecordLimit) 
        sv.ClientID,
        sv.VendorID,
        sv.ProductID,
        sv.TransactionTime,
        sv.ClientName,
        sv.VendorName,
        sv.ProductName,
        sv.ProductCode,
        sv.VendorCode,
FROM SortedReportiew AS sv 
WHERE (sv.ClientID = @ClientID)
    AND (sv.TransactionTime >= @FromDate)
    AND (sv.TransactionTime < @Date)

Update:

Is the below part correct? ref from here

ORDER BY 
    CASE @OrderBy WHEN 'ProductCode ASC' THEN ProductCode WHEN 'ProductCode DESC' THEN ProductCode END DESC,
    CASE @OrderBy WHEN 'ProductName ASC' THEN ProductName WHEN 'ProductName DESC' THEN ProductName END DESC,

Song

Why not dynamic SQL? It's much faster than any of the static solutions for this kind of problem. – Broome

Why do you want to avoid dynamic SQL to run a dynamic statement? – Loveland

@Broome , @Markus Winand , simply because client want to convert those dynamic SQL's to static – Song

So, ask the client why to do that ;) There is the "dynamic SQL is slow myth" that leads many times to requests like this. However, correctly done dynamic-sql (using bind-paramters) is typically better. – Loveland

@RBarryYoung: getting rid of dynamic sql for "order by" results in better performance across these order by queries. – Tanka

Answer 1

As you already said: Use ORDER BY CASE, but multiple times to avoid the problems with different column types:

...
ORDER BY 
 CASE WHEN @OrderBy ='ProductName ASC' THEN sv.ProductName END,
 CASE WHEN @OrderBy ='ProductName DESC' THEN sv.ProductName END DESC,
 CASE WHEN @OrderBy ='ProductCode ASC' THEN sv.ProductCode END,
 CASE WHEN @OrderBy ='ProductCode DESC' THEN sv.ProductCode END DESC,
 CASE WHEN @OrderBy ='VendorName ASC' THEN sv.VendorName END,
 CASE WHEN @OrderBy ='VendorName DESC' THEN sv.VendorName END DESC,
 CASE WHEN @OrderBy ='VendorCode' THEN sv.VendorCode END,
 CASE WHEN @OrderBy ='ClientName' THEN sv.ClientName END

EDIT:

Updated the query to fit your updated question. I assume you meant ProductCode ASC and ProductCode DESC?

Steelworker

Please see updated question, @OrderBy contains sort direction too like ProductName ASC – Song

ASC/DESC is optional, with ASC being the default: msdn.microsoft.com/en-us/library/ms188385.aspx – Halley

Answer 2

I understand, the ordering uses just one column. In that case I might try something like this:

1. Split @OrderBy into @OrderByCol and @OrderByDir.

2. Use this template:

   ...
   ORDER BY
     CASE @OrderByDir WHEN 'ASC' THEN
       CASE @OrderByCol
         WHEN 'Column1' THEN Column1
         WHEN 'Column2' THEN Column2
         ...
       END
     END ASC,
   
     CASE @OrderByDir WHEN 'DESC' THEN
       CASE @OrderByCol
         WHEN 'Column1' THEN Column1
         WHEN 'Column2' THEN Column2
         ...
       END
     END DESC
   

   Or, if you are on SQL Server 2005+, maybe this one, as an alternative:

   WITH sorted AS (
     SELECT
        ... /* columns, omitted */
        Column1Order = ROW_NUMBER() OVER (ORDER BY Column1),
        Column2Order = ROW_NUMBER() OVER (ORDER BY Column2),
        ...
     FROM ...
   )
   SELECT ...
   FROM sorted
   ORDER BY
     CASE @OrderByCol
       WHEN 'Column1' THEN Column1Order
       WHEN 'Column2' THEN Column2Order
       ...
     END * CASE @OrderByDir WHEN 'DESC' THEN -1 ELSE 1 END
   

As @Greg Ogle has correctly pointed out in the comment, the first template can only work when the various sorting criteria are of compatible types, otherwise the statement will break.

Astraddle

Note that the ORDER BY's must be of the same data type per CASE, otherwise it will get a conversion error. – Knowle

Thanks, I added the note to my answer (don't know why I failed to do so when I was posting the answer). – Astraddle