排序总是返回相同的结果 [英] Sorting always returns the same result

问题描述

我有以下查询

Create procedure usp_GetBills
@PageNo         INT = 1,
@PageSize       INT = 10,
@SortOrder      INT = 1,
@SortColumn     VARCHAR(20) = ''
AS
BEGIN
DECLARE
@lSortColumn   VARCHAR(20),
@lFirstRec      INT,
@lLastRec       INT

SET @SortColumn = LTRIM(RTRIM(@SortColumn))
SET @lFirstRec  = (@PageNo - 1) *@PageSize
SET @lLastRec   = (@PageNo * @PageSize + 1)

;WITH CTE_Results 
AS(
SELECT ROW_NUMBER() OVER (ORDER BY(
@SortColumn))
AS ROWNUM,P.BillNo, P.PropertyNo, P.BillDate,  P.BillFromDate, P.BillToDate, P.BillAmount, P.DueDate, P.Status 
FROM Bill P)
SELECT * from CTE_Results
WHERE ROWNUM > @lFirstRec
AND ROWNUM   < @lLastRec
ORDER BY 
--ROWNUM * @SortOrder
CASE WHEN @SortOrder = 1 THEN @SortColumn ELSE ''  END ASC,
CASE WHEN @SortOrder = 2 THEN @SortColumn ELSE ''  END DESC

结束

所以如果我执行

DECLARE @return_value int
    EXEC    @return_value = [dbo].[usp_GetBills]
    @PageNo = 2,
    @PageSize = 10,
    @SortOrder =  N'1',
    @SortColumn = 'BillNo'
    SELECT  'Return Value' = @return_value

这个查询总是给我相同的结果 w.r.t SortOrder.SortOrder 似乎无法正常工作.我提到了此链接

This query gives me the same result always w.r.t SortOrder. The SortOrder doesn't seems to work properly. I referred this link

谢谢.

推荐答案

we cant pass dynamic columns in order by clause

we cant pass dynamic columns in order by clause

所以这个

 Create procedure usp_GetBills
@PageNo         INT = 1,
@PageSize       INT = 10,
@SortOrder      INT = 1,
@SortColumn     VARCHAR(20) = ''
AS
BEGIN
DECLARE
@lSortColumn   VARCHAR(20),
@lFirstRec      INT,
@lLastRec       INT

SET @SortColumn = LTRIM(RTRIM(@SortColumn))
SET @lFirstRec  = (@PageNo - 1) *@PageSize
SET @lLastRec   = (@PageNo * @PageSize + 1)

;WITH CTE_Results 
AS(
SELECT ROW_NUMBER() OVER (ORDER BY(
@SortColumn))
AS ROWNUM,P.BillNo, P.PropertyNo, P.BillDate,  P.BillFromDate, P.BillToDate, P.BillAmount, P.DueDate, P.Status 
FROM Bill P)
SELECT * from CTE_Results
WHERE ROWNUM > @lFirstRec
AND ROWNUM   < @lLastRec
ORDER BY 
--ROWNUM * @SortOrder
CASE WHEN @SortIndex = 1 THEN BillNo END ASC,
CASE WHEN @SortIndex = 2 THEN BillNo END DESC, 
CASE WHEN @SortIndex = 1 THEN PropertyNo END ASC 
CASE WHEN @SortIndex = 2 THEN PropertyNo END DESC 

END

这篇关于排序总是返回相同的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！