title: Pagination with the stored procedure
body: <p>I am trying to add sorting feature of my pagination stored procedure.</p>
  
  <p>How can I do this, so far I created this one. It works fine but when pass the <code>@sort</code> parameter, it didn't work.</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[sp_Mk]
   @page INT,
   @size INT,
   @sort nvarchar(50) ,
   @totalrow INT  OUTPUT
  AS
  BEGIN
      DECLARE @offset INT
      DECLARE @newsize INT
  
      IF(@page=0)
      begin
         SET @offset = @page;
         SET @newsize = @size
      end
      ELSE 
      begin
          SET @offset = @page+1;
          SET @newsize = @size-1
      end
      -- SET NOCOUNT ON added to prevent extra result sets from
      SET NOCOUNT ON;
      WITH OrderedSet AS
      (
        SELECT *,
            ROW_NUMBER() OVER (ORDER BY @sort DESC) AS 'Index'
        FROM [dbo].[Mk]  
      )
     SELECT * 
     FROM OrderedSet 
     WHERE [Index] BETWEEN @offset AND (@offset + @newsize) 
  
     SET @totalrow = (SELECT COUNT(*) FROM [dbo].[Mk])
  END
  </code></pre>