启用排序和分页的 T-SQL 存储过程无法正常工作 [英] T-SQL stored procedure with sorting and paging enabled not working properly
本文介绍了启用排序和分页的 T-SQL 存储过程无法正常工作的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在使用以下代码:
ALTER PROCEDURE [dbo].[usp_get_all_groups]
-- Add the parameters for the stored procedure here
@pStartIndex smallint,
@pPageSize tinyint,
@pOrderBy varchar
AS
BEGIN
SELECT
*
FROM
(SELECT ROW_NUMBER() OVER (ORDER BY
CASE WHEN @pOrderBy='GroupId ASC' THEN UserGroups._id + ' ASC'
WHEN @pOrderBy='GroupId DESC' THEN UserGroups._id + ' DESC'
WHEN @pOrderBy='GroupCode ASC' THEN UserGroups.GroupCode + ' ASC'
WHEN @pOrderBy='GroupCode DESC' THEN UserGroups.GroupCode + ' DESC'
END
) AS Row, * FROM UserGroups)
AS StudentsWithRowNumbers
WHERE Row>= @pStartIndex AND Row <= @pStartIndex + @pPageSize
END
当我使用以下命令执行存储过程时
When I am executing the stored proc using the following command
DECLARE @return_value int
EXEC @return_value = [dbo].[usp_get_all_groups]
@pStartIndex = 0,
@pPageSize = 15,
@pOrderBy = N'GroupCode ASC'
SELECT 'Return Value' = @return_value
我得到了这些未排序的结果.
I am getting these results which is not sorted.
Row _id GroupCode Description Type IsActive
1 1 CS2009 CS 2009 Batch S 1
2 2 IT2009 IT 2009 Batch S 1
3 3 ME2009 ME 2009 Batch S 1
4 4 EC2009 EC 2009 Batch S 1
5 5 EE2009 EE 2009 Batch S 1
6 8 CS_F CS Faculties F 1
7 9 IT_F IT Faculties F 1
8 10 ME_F ME Faculties F 1
9 11 EC_F EC Faculties F 1
10 12 EE_F EE Faculties F 1
11 13 BSC_F Basic Science Faculties F 1
12 14 Accounts Accounts A 1
13 15 Mgmt Management M 1
14 16 Lib Library B 1
15 17 TnP Training & Placement T 1
你能告诉我还需要什么吗?
Can you tell me what else is required?
我已经尝试过这个,但它也给出了平面未排序的结果:
I have tried this, but it is also giving plane unsorted result:
SELECT
GroupTable._id,
GroupTable.GroupCode,
GroupTable.Type,
GroupTable.Description
FROM
(SELECT ROW_NUMBER() OVER (ORDER BY
CASE WHEN @pOrderBy='GroupId ASC' THEN CONVERT(varchar(20), '_id ASC')
WHEN @pOrderBy='GroupId DESC' THEN CONVERT(varchar(20), '_id DESC')
WHEN @pOrderBy='GroupCode ASC' THEN CONVERT(varchar(20), @pOrderBy)
WHEN @pOrderBy='GroupCode DESC' THEN CONVERT(varchar(20), @pOrderBy)
END
) AS Row, * FROM UserGroups)
AS GroupTable
WHERE Row>= @pStartIndex AND Row <= @pStartIndex + @pPageSize
Select COUNT(*) as TotalRows from UserGroups where IsActive= 1
推荐答案
用这个替换你的程序:
ALTER PROCEDURE [dbo].[usp_get_all_groups]
-- Add the parameters for the stored procedure here
@pStartIndex smallint,
@pPageSize tinyint,
@pOrderBy varchar(15)
AS
BEGIN
SELECT *
FROM
(SELECT ROW_NUMBER() OVER (ORDER BY
CASE WHEN @pOrderBy='GroupId ASC' THEN UserGroups._id END ASC,
CASE WHEN @pOrderBy='GroupId DESC' THEN UserGroups._id END DESC,
CASE WHEN @pOrderBy='GroupCode ASC' THEN UserGroups.GroupCode END ASC,
CASE WHEN @pOrderBy='GroupCode DESC' THEN UserGroups.GroupCode END DESC) AS Row,
* FROM UserGroups) AS StudentsWithRowNumbers
WHERE Row>= @pStartIndex AND Row <= @pStartIndex + @pPageSize
ORDER BY Row
END
您不能将 asc 和 desc 动态分配给非动态表达式.
You can't dynamically assign asc and desc to a non-dynamic expression.
这篇关于启用排序和分页的 T-SQL 存储过程无法正常工作的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文