[SOLVED] how to pass Order By @col name through variable in a paging enabled Query

how to pass Order By @col name through variable in a paging enabled Query

Following is a Paging enabled Query for SQL server, it is working well without dynamic order by @pOrderBy variable .

I want to implement two things in this query.

I want to sort the rows according to the column name i m passing.

I want to run another Query in it to get total number of rows so that I can display number of pages on my UI.

ALTER PROCEDURE [dbo].[usp_get_all_groups] 
  -- Add the parameters for the stored procedure here
  @pStartIndex smallint,
  @pPageSize tinyint,
  @pOrderBy varchar(20)
AS
BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;

 SELECT       GroupTable._id,
              GroupTable.GroupCode,
              GroupTable.Type,
              GroupTable.Description
    From(
    Select ROW_NUMBER() OVER (
       ORDER BY UG._id,
            UG.GroupCode,
            UG.Type,
            UG.Description ) as [Row_Number],
            UG._id,
            UG.GroupCode,
            UG.Type,
            UG.Description
   From UserGroups as UG
   ) as GroupTable
   where GroupTable.[Row_Number] BETWEEN @pStartIndex AND @pStartIndex + @pPageSize             
   ORDER BY GroupTable.[Row_Number]

END

Solution

I want to sort the rows according to the column name i m passing.

You will need to use dynamic SQL for that - it is not possible to use a column name in a variable on a regular query.

An alternative is to use CASE in the ORDER BY clause - checking the value of the variable and using the corresponding column. See the comment from @Dems.

I want to run another Query in it to get total number of rows so that I can display number of pages on my UI.

Use an output parameter or return value and assign the value of such a query to it in this stored procedure.