C # commonly used paging stored procedures

CREATEProcedureFramWorkPage @ TableNamevarchar (50), -- table name @ Fieldsvarchar (5000) *, -- field name (all fields are *) @ OrderFieldvarchar (5000), -- Sort field (required! Supports multiple fields) @ sqlWherevarchar (5000) Null, -- Condition Statement (do not CREATE Procedure FramWorkPage
@ TableName varchar (50), -- table name
@ Fields varchar (5000) = '*', -- field name (all Fields are *)
@ OrderField varchar (5000), -- Sort field (required! Supports multiple fields)
@ SqlWhere varchar (5000) = Null, -- Condition Statement (where is not required)
@ PageSize int, -- number of records per page
@ PageIndex int = 1, -- specifies the current page number
@ TotalPage int output, -- number of returned results
@ OrderType bit -- set the sorting type. If the value is 1 in ascending order and the value is 0 in descending order
As
Begin
Declare @ strOrder varchar (400) -- sort type

Begin Tran -- start transaction
Declare @ SQL nvarchar (4000 );
Declare @ totalRecord int;
-- Calculate the total number of records
If (@ SqlWhere = ''' or @ sqlWhere is NULL)
Set @ SQL = 'select @ totalRecord = count (*) from' + @ TableName
Else
Set @ SQL = 'select @ totalRecord = count (*) from '+ @ TableName + 'where' + @ sqlWhere
EXEC sp_executesql @ SQL, n' @ totalRecord int output', @ totalRecord OUTPUT -- calculate the total number of records

-- Calculate the total number of pages

Select @ TotalPage = @ totalRecord -- CEILING (@ totalRecord + 0.0)/@ PageSize)

If @ OrderType = 0
Begin
Set @ strOrder = 'order by ['+ @ OrderField +'] desc'
-- If @ OrderType is 0, execute the descending order. This sentence is very important!
End
Else
Begin
Set @ strOrder = 'order by ['+ @ OrderField +'] asc'
End

If (@ SqlWhere = ''' or @ sqlWhere is NULL)
Set @ SQL = 'select * FROM (Select ROW_NUMBER () Over ('+ @ strOrder +') as rowId, '+ @ Fields + 'from' + @ TableName
Else
Set @ SQL = 'select * FROM (Select ROW_NUMBER () Over ('+ @ strOrder +') as rowId, '+ @ Fields + 'from' + @ TableName + 'where' + @ SqlWhere
-- Processing page number out of range
If @ PageIndex <= 0
Set @ pageIndex = 1

If @ pageIndex> @ TotalPage
Set @ pageIndex = @ TotalPage

-- Process start and end points
Declare @ StartRecord int
Declare @ EndRecord int

Set @ StartRecord = (@ pageIndex-1) * @ PageSize + 1
Set @ EndRecord = @ StartRecord + @ pageSize-1

If @ OrderType = 0
Begin
Set @ strOrder = 'order by rowid desc'
-- If @ OrderType is 0, execute the descending order. This sentence is very important!
End
Else
Begin
Set @ strOrder = 'order by rowid asc'
End
-- Continue merging SQL statements
Set @ SQL = @ SQL + ') as' + @ TableName + 'where rowId between' + Convert (varchar (50), @ StartRecord) + 'and' + Convert (varchar (50), @ EndRecord) + ''+ @ strOrder
-- Print @ SQL
Exec (@ SQL)
---------------------------------------------------
If @ Error <> 0
Begin
RollBack Tran
Return-1
End
Else
Begin
Commit Tran
Return @ totalRecord --- total number of returned records
End
End
Select top 10 *
FROM TestTable
WHERE (ID>
(Select max (id)
FROM (select top 20 id
FROM TestTable
Order by id) as t ))
ORDER BY ID

Select top page size *
FROM TestTable
WHERE (ID>
(Select max (id)
FROM (select top page size * Page id
FROM table
Order by id) as t ))
ORDER BY ID

Select top 10 *
FROM TestTable
WHERE (ID NOT IN
(Select top 20 id
FROM TestTable
Order by id ))
ORDER BY ID

Select top page size *
FROM TestTable
WHERE (ID NOT IN
(Select top page size * Page id
FROM table
Order by id ))
ORDER BY ID

Create procedure SqlPager
@ Sqlstr nvarchar (4000), -- query string
@ Currentpage int, -- page N
@ Pagesize int -- number of lines per page
As
Set nocount on
Declare @ P1 int, -- P1 is the cursor id
@ Rowcount int
Exec sp_cursoropen @ P1 output, @ sqlstr, @ scrolopt = 1, @ ccopt = 1, @ rowcount = @ rowcount output
Select ceiling (1.0 * @ rowcount/@ pagesize) as total number of pages --, @ rowcount as total number of rows, @ currentpage as current page
Set @ currentpage = (@ currentpage-1) * @ pagesize + 1
Exec sp_cursorfetch @ P1, 16, @ currentpage, @ pagesize
Exec sp_cursorclose @ P1
Set nocount off