How to use Order By in a stored procedure without using dynamic SQL
Solution 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
?
Solution 2
I understand, the ordering uses just one column. In that case I might try something like this:
Split
@OrderBy
into@OrderByCol
and@OrderByDir
.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.
Comments
-
Mithun Sreedharan about 2 years
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
orClientName
I was trying to use
ORDER BY CASE
, is there any issue if theProductName
,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,
-
Mithun Sreedharan about 13 yearsPlease see updated question, @OrderBy contains sort direction too like
ProductName ASC
-
Valentino Vranken about 13 yearsASC/DESC is optional, with ASC being the default: msdn.microsoft.com/en-us/library/ms188385.aspx
-
oglester almost 12 yearsNote that the ORDER BY's must be of the same data type per CASE, otherwise it will get a conversion error.
-
Andriy M almost 12 yearsThanks, I added the note to my answer (don't know why I failed to do so when I was posting the answer).