Sorting in grid, SP issue

USE [Game_test]
GO
/****** Object:  StoredProcedure [dbo].[GetCustomersPageWise]    Script Date: 11/15/2013 23:46:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
ALTER  PROCEDURE [dbo].[GetCustomersPageWise]
      @PageIndex INT = 1
      ,@PageSize INT = 10
      ,@OrderBy varchar(15)
      ,@RecordCount INT OUTPUT
AS
BEGIN
      SET NOCOUNT ON;
      SELECT ROW_NUMBER() OVER
      (
            ORDER BY @OrderBy  ASC
      )AS RowNumber
      ,[Playerid]
      ,[playername]
      ,[designation]
     INTO #Results
      FROM [player]
     
      SELECT @RecordCount = COUNT(*)
      FROM #Results
           
      SELECT * FROM #Results
      WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
     
      DROP TABLE #Results
END