Store Procedure of Paging,Sorting and Searching.

--================================================
--Create By : Nilay Mistry
--LIMS Application
--Paging,Sorting
--Indusa Infotech Service Pvt Ltd
--================================================
CREATE PROCEDURE getSelectedPage
    @TableOrView nvarchar (50),             
    @SelectedPage int,
    @PageSize int,
    @Columns nvarchar(500),
    @OrderByColumn nvarchar(100),
    @OrderByDirection nvarchar(4),
    @WhereClause nvarchar(500)
AS
DECLARE @ReturnedRecords int, @SqlQuery nvarchar(1000), @ConOrderByDirection nvarchar(4)
IF Upper(@OrderByDirection) = 'ASC'
  BEGIN
   
    SET @ConOrderByDirection = 'DESC'
  END
ELSE
  BEGIN
  
    SET @ConOrderByDirection = 'ASC'
   
  END

IF @WhereClause <> ''
  BEGIN
    SET @WhereClause = ' WHERE ' + @WhereClause
  END

SET @ReturnedRecords = (@PageSize * @SelectedPage) -1
SET NOCOUNT ON

    SET @SqlQuery = N'SELECT * FROM '+ @TableOrView +' where  ' + @Columns + ' in
       (SELECT TOP ' + CAST(@ReturnedRecords as varchar(10)) + ' ' + @Columns +
         ' FROM ' + @TableOrView + @WhereClause + '
         ORDER BY ' + @OrderByColumn + ' ' + @OrderByDirection + ') Order by
         '+ @Columns + ' ' + @OrderByDirection + ''
      
    --SELECT @SqlQuery
   
EXEC(@SqlQuery)
SET NOCOUNT OFF