大家好我米以下使用code

  ALTER PROCEDURE [DBO]。[usp_get_all_groups]
     - 添加的参数存储过程在这里
    @pStartIndex SMALLINT，
    @pPageSize TINYINT，
    @pOrderBy VARCHAR
如
开始

 选择
       *
       从
        （SELECT ROW_NUMBER（）OVER（ORDER BY

        CASE WHEN @ pOrderBy ='的GroupId ASC，然后UserGroups._id +ASC
             WHEN @ pOrderBy ='的GroupId DESC，然后UserGroups._id +降序
             WHEN @ pOrderBy ='集团code ASC'，那么UserGroups.Group code +ASC
             WHEN @ pOrderBy ='集团code降序，然后UserGroups.Group code +'DESC'
        结束
        ）为行，* FROM群组）
       AS StudentsWithRowNumbers
         WHERE行＆GT; = @pStartIndex和行＆LT; = @pStartIndex + @pPageSize
结束
 

当我使用下面的命令执行存储过程

 定义@return_value诠释

EXEC @return_value = [DBO]。[usp_get_all_groups]
        @pStartIndex = 0，
        @pPageSize = 15，
        @pOrderBy = N'Group code ASC'

选择返回值= @return_value
 

我得到这些结果是没有排序。

 行_id集团code说明类型IsActive
1 CS2009 CS 2009年批的1
2 2 IT2009 2009年的IT组S 1
3 3 ME2009 ME 2009年批的1
4 4 EC2009 EC 2009年批的1
5 EE2009 EE 2009年批的1
6月8日CS_F CS院系˚F1
7月9日IT_F IT院系˚F1
8 10 ME_F ME院系˚F1
9月11日EC_F EC院系˚F1
10月12日EE_F EE院系˚F1
11月13日BSC_F基础科学院系˚F1
12月14日应收帐户的1
13 15管理管理M 1
14 16库库b 1分配
15 17 TNP培训与放置牛逼1
 

你能告诉我还需要什么？

我已经试过这一点，但它也给飞机不排序结果

  SELECT
        GroupTable._id，
        GroupTable.Group code，
        GroupTable.Type，
        GroupTable.Description
       从
        （SELECT ROW_NUMBER（）OVER（ORDER BY

        CASE WHEN @ pOrderBy ='的GroupId ASC再转换（VARCHAR（20），_id ASC）
             WHEN @ pOrderBy ='的GroupId DESC再转换（VARCHAR（20），_id DESC）
             WHEN @ pOrderBy ='集团code ASC'再转换（VARCHAR（20），@pOrderBy）
             WHEN @ pOrderBy ='集团code DESC'再转换（VARCHAR（20），@pOrderBy）
        结束
        ）为行，* FROM群组）
       AS grouptable将
         WHERE行＆GT; = @pStartIndex和行＆LT; = @pStartIndex + @pPageSize

       SELECT COUNT（*）从群组TotalRows，其中IsActive = 1
 

解决方案

替换你的程序有这样的：

  ALTER PROCEDURE [DBO]。[usp_get_all_groups]
     - 添加的参数存储过程在这里
    @pStartIndex SMALLINT，
    @pPageSize TINYINT，
    @pOrderBy VARCHAR（15）
如
开始

 选择 *
 从
  （SELECT ROW_NUMBER（）OVER（ORDER BY
      CASE WHEN @ pOrderBy ='的GroupId ASC'，那么UserGroups._id END ASC，
      CASE WHEN @ pOrderBy ='的GroupId DESC'，那么UserGroups._id END DESC，
      CASE WHEN @ pOrderBy ='集团code ASC'，那么UserGroups.Group code END ASC，
      CASE WHEN @ pOrderBy ='集团code降序，然后UserGroups.Group code END DESC）为行，
      * FROM群组）AS StudentsWithRowNumbers
  WHERE行＆GT; = @pStartIndex和行＆LT; = @pStartIndex + @pPageSize
  ORDER BY行
结束
 

您不能动态地分配ASC和DESC到非动态EX pression。

Hi guys I m using following Code

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

Replace your procedure with this:

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

You can't dynamically assign asc and desc to a non-dynamic expression.