title: T-SQL stored procedure with sorting and paging enabled not working properly
body: <p>I am using the following code:</p>
  <pre><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&gt;= @pStartIndex AND Row &lt;= @pStartIndex + @pPageSize
  END
  </code></pre>
  <p>When I am executing the stored proc using the following command</p>
  <pre><code>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
  </code></pre>
  <p>I am getting these results which is not sorted.</p>
  <pre><code>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 &amp; Placement    T   1
  </code></pre>
  <p>Can you tell me what else is required?</p>
  <hr />
  <p>I have tried this, but it is also giving plane unsorted result:</p>
  <pre><code>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&gt;= @pStartIndex AND Row &lt;= @pStartIndex + @pPageSize
         
         Select COUNT(*) as TotalRows from UserGroups where IsActive= 1
  </code></pre>