T-SQL stored procedure with sorting and paging enabled not working properly

Question

I am using the 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

Answer 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.

Answer 2

There seems to be the misconception that the expression

ORDER BY UserGroups._id + ' DESC'

will cause SQL Server to apply a descending order. What it really does is simply append the string literal " DESC" to the column value and then sort the result in ascending order.

You need to dynamically create the whole SELECT statement in the procedure, applying the ORDER BY twice as sketched in marc_s' answer, and execute the statement using sp_executesql. sp_executesql also allows you to pass the @ parameters.