sql: paging in SQL Server

--sql server 2012 及以上  涂聚文 Geovin Du geovindu 注
SELECT  * FROM BookKindList
ORDER BY (SELECT NULL)
OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY
go
 
-- geovindu Geovin Du 涂聚文
Declare @PageNo INT
Declare @PageSize INT
Set @PageNo=1
Set @PageSize=4
Select * From (Select ROW_NUMBER() Over (Order by BookKindID Desc) AS 'RowNum',*
From BookKindList Where BookKindID > 0)t Where t.RowNum Between
((@PageNo-1)*@PageSize +1) AND (@PageNo*@pageSize)
go
 
Declare @PageNo INT
Declare @PageSize INT
Set @PageNo=2
Set @PageSize=4
Select * From (Select ROW_NUMBER() Over (Order by BookKindID Desc) AS 'RowNum',*
From BookKindList Where BookKindID > 0)t Where t.RowNum Between
((@PageNo-1)*@PageSize +1) AND (@PageNo*@pageSize)
go
 
 
 
 
 
-- 2 - QUERY USING "ROW_NUMBER" 涂聚文 Geovin Du geovindu
DECLARE @PageNumber AS INT, @RowspPage AS INT
SET @PageNumber = 1
SET @RowspPage = 4
SELECT * FROM (
             SELECT ROW_NUMBER() OVER(ORDER BY BookKindID) AS Numero,
                    * FROM BookKindList
               ) AS TBL
WHERE BookKindID BETWEEN ((@PageNumber - 1) * @RowspPage + 1) AND (@PageNumber * @RowspPage)
ORDER BY BookKindID
GO
 
 
 
-- 3 - QUERY USING "OFFSET" AND "FETCH NEXT" (SQL SERVER 2012) Geovin Du geovindu 涂聚文
DECLARE @PageNumber AS INT, @PageSize AS INT
SET @PageNumber = 1
SET @PageSize = 4
SELECT *
FROM BookKindList
ORDER BY BookKindID
OFFSET ((@PageNumber - 1) * @PageSize) ROWS
FETCH NEXT @PageSize ROWS ONLY
GO
 
 
DECLARE @PageNumber AS INT, @PageSize AS INT,@totalcount INT,@pagecount int,@c int
SET @PageNumber = 2
SET @PageSize = 4
SELECT *
FROM BookKindList
ORDER BY BookKindID
OFFSET ((@PageNumber - 1) * @PageSize) ROWS
FETCH NEXT @PageSize ROWS ONLY;
 
SELECT @totalcount=count(*) FROM BookKindList
--Select @c=@totalcount%@PageSize
--Select @pagecount=@totalcount/@PageSize
SELECT  @totalcount as '总记录'
--if @c>0  
--Select  @pagecount+1 as '共页数'
--else
--Select @pagecount as '共页数'
--Select @c
Select @pagecount=dbo.getPageModulus(@totalcount,@PageSize)
GO
 
--'*'#查询字段  涂聚文 Geovin Du geovindu
--,'bookkindlist'#表名
--,'1=1'#条件
--,'BookKindID desc'#排序
--,1 #页码
--,5 #每页记录数
 
DECLARE
@sql as nvarchar(2000),  ---
@sqlt as nvarchar(2000),
@fields as nVARCHAR(1000),  --要查询的字段，用逗号(,)分隔
@tables as nvarchar(150),        --要查询的表 涂聚文 Geovin Du geovindu
@where as nVARCHAR(2000),  --查询条件
@orderby as nVARCHAR(200),  ---BookKindID desc 排序规则
@pageindex as INT, --查询页码 geovindu
@pageSize as INT,  --每页记录数
@totalcount as INT,    --总记录数 out
@pagecount as INT    --总页数 out
 
set @fields='*';
set @tables='BookKindList';
set @where='1=1';
set @orderby='BookKindID desc'
set @pageindex=1;
set @pageSize=14;
set @sql='SELECT '+@fields+' FROM '+@tables+''
if @where<>''
  set @sql=@sql+' where '+@where
if @orderby<>''
  set @sql=@sql+' ORDER BY '+@orderby+''
 
set @sql=@sql+' OFFSET (('+CONVERT(nVARCHAR(20),@pageindex)+' - 1) * '+CONVERT(nVARCHAR(20),@PageSize)+') ROWS FETCH NEXT '+CONVERT(nVARCHAR(20),@PageSize)+' ROWS ONLY;'
set @sqlt='SELECT @totalcount=count(*) FROM BookKindList'
if @where<>''
   set @sqlt= @sqlt+' where '+@where;
--set @sql=@sql+' Select @pagecount=dbo.getPageModulus(@totalcount,@PageSize);'
print(@sql);
exec(@sql);
EXEC sp_executesql @sqlt,N'@totalcount int OUTPUT',@totalcount OUTPUT
Select @pagecount=dbo.getPageModulus(@totalcount,@PageSize)
Select @pagecount as '总页数',@totalcount as '总记录'
GO
 
 
                Declare @sql nvarchar(4000),@SqlWhere nvarchar(1000),@TableName nvarchar(100);
                Declare @totalRecord int;   
            --Declare @TotalPage int;
                --计算总记录数         geovindu
                set @TableName='BookKindList'
                set @sqlWhere='1=1';
                if (@SqlWhere='' or @sqlWhere=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--计算总记录数
                select @totalRecord
                --计算总页数
               print @Sql
go
 
 
declare @tableName nvarchar(100)
declare @sqlQuery nvarchar(max)
declare @fields varchar(500)
set @tableName = 'BookKindList'
set @fields = 'BookKindName,BookKindParent'
set @sqlQuery = 'select ' + @fields + ' from ' + QUOTENAME(@tableName)
execute sp_executesql @sqlQuery
go