SQL Paging Stored Procedures

ALTER procedure [dbo]. [Fenye]
@pagesize int,--Display quantity per page
@pageCurrent int,--current page
@tablename varchar (20),--table name
@field varchar (20),--displayed column name (eg:id,name)
@where varchar (20),--Filter condition (eg:name NOT NULL)
@orderBy varchar (20),--Sorted column name (eg:id or ID desc)
@count int output--Returns the total number of pages, 0 does not return 1 bits

As
Begin
declare @strSql nvarchar (200)
DECLARE @starNum int
DECLARE @endNum int
Set @starNum = (@pageCurrent-1) * @pagesize
Set @endNum [email protected] * @pagesize
--declare @timediff datetime

--set nocount ON--no count is returned (indicates the number of rows affected by the Transact-SQL statement).
--select @timediff =getdate ()--record time

If @count!=0
begin
If @where = '
Set @strSql = ' Select @count =count (*) from ' [email protected]
else
Set @strSql = ' Select @count =count (*) from ' [email protected]+ ' where ' [email protected]
End
EXEC sp_executesql @strSql, N ' @count int output, @tablename varchar, @where varchar, @count output, @tablename, @ where

If @pageCurrent =1
If @where = '
Set @strSql = ' Select Top ' +cast (@pagesize as varchar) + ' [EMAIL PR otected]+ ' from ' [email protected]+ ' ORDER by ' [email protected]+ '
Else
Set @strSql = ' Select Top ' +cas T (@pagesize as varchar) + ' [email protected]+ ' from ' [email protected]+ ' where ' [email protected]+ ' Order BY ' [email protected]+ '
Else
if @where! = '
Set @strSql = ' SELECT ' [email protected]+ ' from ( Select ' [email protected]+ ', Row_number () over (order by ' [email protected]+ ') rn from ' [email protected ]+ ' where ' [email protected]+ ') A Where rn<= ' +convert (varchar, @endNum) + ' and rn> ' +cast (@starNum as varchar) + '
Else
Set @strSql = ' SELECT ' [email protected]+ ' from (select ' [email protected]+ '], row_number () over (Order by ' [email protected]+ ') rn from ' [email protected]+ ') A Where rn<= ' +convert (varchar, @endNum) + ' and Rn> ' +cast (@starNum as varchar) + '

EXEC (@strSql)

--select DateDiff (MS, @timediff, GETDATE ()) as time-consuming
--set nocount Off--Returns the count (default is off).
End

DECLARE @count int
Set @count =1
exec fenye 3,3,CJ, ' * ', ' Fenshu is not null ', ' ID ', @count output
Select @count

SQL Paging Stored Procedures