CODEWITHSUNDEEP
sql-servert-sqlsql-order-bydynamic-sql
Mithun Sreedharan
Mithun Sreedharan

Reputation: 51292

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

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,

Upvotes: 7

Views: 12210

Answers (2)

Andriy M
Andriy M

Reputation: 77737

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.

Upvotes: 3

Ocaso Protal
Ocaso Protal

Reputation: 20267

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?

Upvotes: 13

Related Questions