我有以下MS sql存储过程.我需要在不使用动态sql和sp_executesql方法的情况下对结果进行排序
@Order by可以具有可能的值ProductName ASC,ProductName DESC,ProductCode ASC,VendorName DESC,VendorCode或ClientName
我试图使用ORDER BY CASE,如果ProductName,ProductCode属于不同类型,是否有任何问题?
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)
更新:
以下部分是否正确?从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,
解决方法
正如您所说:使用ORDER BY CASE,但多次使用以避免不同列类型的问题:
... 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
编辑:
更新了查询以适合您更新的问题.我假设你的意思是ProductCode ASC和ProductCode DESC?