Common paging stored procedures in C #

CREATE Procedure Framworkpage
@TableName varchar (50),--table name
@Fields varchar (5000) = ' * ',--field name (all fields are *)
@OrderField varchar (5000),--Sort fields (must! support multiple fields)
@sqlWhere varchar (5000) = Null,--Conditional statement (don't add where)
@pageSize int,--How many records per page
@pageIndex int = 1,--Specifies the current page
@TotalPage int output,--Returns the number of bars
@OrderType bit--set sort type, 1 ascending 0 value Descending
As
Begin
DECLARE @strOrder varchar (400)--Sort type

Begin Tran--Start a transaction
Declare @sql nvarchar (4000);
Declare @totalRecord int;
--Calculate the total number of records
if (@SqlWhere = ' ' or @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--total number of records calculated

--Calculate Total pages

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

If @OrderType = 0
Begin
Set @strOrder = ' ORDER by [' + @OrderField + '] desc '
If @ordertype is 0, it's important to perform descending order.
End
Else
Begin
Set @strOrder = ' ORDER by [' + @OrderField + '] ASC '
End

if (@SqlWhere = ' ' or @SqlWhere = ' ' or @sqlWhere is NULL)
Set @sql = ' SELECT * FROM (select Row_number () ("+ @strOrder +") as RowId, ' + @Fields + ' from ' + @TableName
Else
Set @sql = ' SELECT * FROM (select Row_number () ("+ @strOrder +") as RowId, ' + @Fields + ' from ' + @TableName + ' wher E ' + @SqlWhere
--handling pages out of range
If @PageIndex <=0
Set @pageIndex = 1

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

--processing 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, it is important!
End
Else
Begin
Set @strOrder = ' ORDER by rowid ASC '
End
--continues to synthesize SQL statements
Set @Sql = @Sql + ' as ' + @TableName + ' where rowId between ' + convert (varchar), @StartRecord) + ' and ' + CONVERT (varchar), @EndRec ORD) + ' + @strOrder
--Print @sql
Exec (@Sql)
---------------------------------------------------
If @ @Error <> 0
Begin
RollBack Tran
Return-1
End
Else
begin
Commit Tran
Return @totalRecord---Returns the total number of records
End
End
Select top *
from testtable
WHERE (Id >
(select MAX (ID)
from (SELECT up 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 * Pages ID
From table
Order by ID) as T)
ORDER BY ID

SELECT Top 10 *
From TestTable
WHERE (ID not in
(SELECT Top ID
From TestTable
Order by ID)
ORDER BY ID

SELECT Top Page Size *
From TestTable
WHERE (ID not in
(SELECT top Page size * Pages ID
From table
Order by ID)
ORDER BY ID

CREATE PROCEDURE SqlPager
@sqlstr nvarchar (4000),--query string
@currentpage int,--nth page
@pagesize INT--Every Number of page rows
as
SET NOCOUNT on
declare @P1 int,--P1 is the ID of the cursor
@rowcount int
exec sp_cursoropen @P1 output, @sq Lstr, @scrollopt =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,@c Urrentpage, @pagesize
exec sp_cursorclose @P1
SET NOCOUNT off