嗨,大家好我使用以下代码
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
当我使用以下命令执行存储过程时
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
我得到的这些结果没有排序.
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
你能告诉我还需要什么吗?
我试过这个,但它也给了飞机未分类的结果
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,@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动态分配给非动态表达式.