SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE sp_Easypaging
(
@TABLES VARCHAR(1000),
@PK VARCHAR(100),
@JoinStatements VARCHAR(1000)='',
@FIELDS VARCHAR(5000) = '*',
@FILTER VARCHAR(5000) = NULL,
@Sort VARCHAR(200) = NULL,
@PageNumber INT = 1,
@PageSize INT = 10,
@TotalRec INT =0 Output,
@GROUP VARCHAR(1000) = NULL
)
AS
/*
Created by Kashif Akram
Email Muhammad_kashif@msn.com
The publication rights are reserved
You can use this procedure with out removing these comments
*/
DECLARE @strPageSize VARCHAR(50)
DECLARE @strStartRow VARCHAR(50)
SET @strPageSize = CAST(@PageSize AS VARCHAR(50))
SET @strStartRow = CAST(((@PageNumber - 1)*@PageSize + 1) AS VARCHAR(50))
--set @PK =' tbl_Items.ItemID '
CREATE TABLE #PageTable (PID BIGINT PRIMARY KEY IDENTITY (1, 1) , UID INT)
CREATE TABLE #PageIndex (UID INT)
/*
CREATE UNIQUE CLUSTERED
INDEX [PK_tbl_PageTable] ON #PageTable (PID)
*/
CREATE
INDEX [PK_tbl_PageIndex] ON #PageIndex (UID)
--'SELECT ' + @Fields + ' FROM ' + @Tables + '' + @JoinStatements +' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + ' DESC '
EXEC ('
set rowcount 0
insert into #pageTable(UID)
SELECT ' + @PK + ' FROM ' + @TABLES + ' ' + @JoinStatements +' WHERE ' + @FILTER + ' ' + @GROUP + ' ORDER BY ' + @Sort + '
DECLARE @SortColumn int
SET ROWCOUNT '+ @strStartRow +'
select @SortColumn=PID from #PageTable --option (keep plan)
print @SortColumn
SET ROWCOUNT '+ @strPageSize +'
insert into #pageIndex
select UID from #PageTable where PID >= @SortColumn -- option (keep plan)
SELECT ' + @FIELDS + ' FROM ' + @TABLES + ' ' + @JoinStatements +' WHERE ' + @FILTER + ' and '+ @PK + ' in (Select UID from #pageIndex)' + @GROUP + ' ORDER BY ' + @Sort + ' '
)
SELECT @TotalRec=COUNT(*) FROM #pageTable
DROP TABLE #PageTable
DROP TABLE #PageIndex
RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
//SQL/2380