本文介绍了排序总是返回相同的结果的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有以下查询
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
这篇关于排序总是返回相同的结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!