USE Hotel
go
--带偏移量的适合首页数据的存储过程
CREATE PROC PaginationWithOffsetInFirstPage
@columns VARCHAR(500) , --要显示的列名,用逗号隔开
@tableName VARCHAR(100) , --要查询的表名
@orderColumnName VARCHAR(100) , --排序的列名
@order VARCHAR(50) , --排序的方式,升序为asc,降序为 desc
@where VARCHAR(100) , --where 条件,如果不带查询条件,请用 1=1
@pageIndex INT , --当前页索引
@pageSize INT , --页大小(每页显示的记录条数)
@offset INT ,--偏移量(页中如果有别的数据需要插入,就赋此参数)
@pageCount INT OUTPUT , --总页数,输出参数
@totalCount INT OUTPUT --总记录数,输出参数
AS
BEGIN
DECLARE @sqlRecordCount NVARCHAR(1000) --得到总记录条数的语句
DECLARE @sqlSelect NVARCHAR(1000) --查询语句
SET @sqlRecordCount = N'select @recordCount=count(*) from '
+ @tableName + ' where ' + @where
DECLARE @recordCount INT --保存总记录条数的变量
EXEC sp_executesql @sqlRecordCount, N'@recordCount int output',
@recordCount OUTPUT
--动态 sql 传参
SET @totalCount = @recordCount --把总记录数赋给输出参数
IF ( @recordCount % @pageSize = 0 ) --如果总记录条数可以被页大小整除
SET @pageCount = @recordCount / @pageSize --总页数就等于总记录条数除以页大小
ELSE --如果总记录条数不能被页大小整除
SET @pageCount = @recordCount / @pageSize + 1 --总页数就等于总记录条数除以页大小加1
SET @sqlSelect = N'select top' + STR(@pageSize - @offset) + @columns
+ ' from (
select row_number() over (order by ' + @orderColumnName + ' ' + @order
+ ') as rowId,* from ' + @tableName + ' where ' + @where
+ ') as A where A.rowId >' + STR(( @pageindex - 1 ) * @pageSize)
--STR(( @pageIndex - 1 )* @pageSize + 1) + ' and '
--+ STR(@pageIndex * @pageSize)
EXEC (@sqlSelect) --执行动态Sql
END
go
--以下是调用示例
USE Hotel
go
CREATE PROC PaginationWithOffsetNotInFirstPage
@columns VARCHAR(500) , --要显示的列名,用逗号隔开
@tableName VARCHAR(100) , --要查询的表名
@orderColumnName VARCHAR(100) , --排序的列名
@order VARCHAR(50) , --排序的方式,升序为asc,降序为 desc
@where VARCHAR(100) , --where 条件,如果不带查询条件,请用 1=1
@pageIndex INT , --当前页索引
@pageSize INT , --页大小(每页显示的记录条数)
@offset INT ,--偏移量(页中如果有别的数据需要插入,就赋此参数,默认为零)
@pageCount INT OUTPUT , --总页数,输出参数
@totalCount INT OUTPUT --总记录数,输出参数
AS
BEGIN
DECLARE @sqlRecordCount NVARCHAR(1000) --得到总记录条数的语句
DECLARE @sqlSelect NVARCHAR(1000) --查询语句
SET @sqlRecordCount = N'select @recordCount=count(*) from '
+ @tableName + ' where ' + @where
DECLARE @recordCount INT --保存总记录条数的变量
EXEC sp_executesql @sqlRecordCount, N'@recordCount int output',
@recordCount OUTPUT
--动态 sql 传参
SET @totalCount = @recordCount --把总记录数赋给输出参数
IF ( @recordCount % @pageSize = 0 ) --如果总记录条数可以被页大小整除
SET @pageCount = @recordCount / @pageSize --总页数就等于总记录条数除以页大小
ELSE --如果总记录条数不能被页大小整除
SET @pageCount = @recordCount / @pageSize + 1 --总页数就等于总记录条数除以页大小加1
SET @sqlSelect = N'select top' + STR(@pageSize) + @columns + ' from (
select row_number() over (order by ' + @orderColumnName + ' ' + @order
+ ') as rowId,* from ' + @tableName + ' where ' + @where
+ ') as A where A.rowId >' + STR(( @pageindex - 1 ) * @pageSize
- @offset)
--STR(( @pageIndex - 1 )* @pageSize + 1) + ' and '
--+ STR(@pageIndex * @pageSize)
EXEC (@sqlSelect) --执行动态Sql
END
go
USE Hotel
go
--调用首页数据的存储过程,page必须传1
DECLARE @d DATETIME
SET @d = GETDATE()
DECLARE @pageCount INT
DECLARE @totalCount NVARCHAR(1000)
EXECUTE dbo.PaginationWithOffsetInFirstPage 'rowid,[hotelId],[hotelName]', -- varchar(500)
'dbo.TB_HotelList', -- varchar(100)
'hotelId', -- varchar(100)
'asc', -- varchar(50)
'1=1 and cityId=215', -- varchar(100)
1, -- int
20, -- int
5,
@pageCount OUTPUT, -- int
@totalCount OUTPUT
SELECT STR(@pageCount) ,
@totalCount
SELECT '语句执行花费时间(毫秒)' = DATEDIFF(ms, @d, GETDATE())
go
--调用非首页数据的存储过程
DECLARE @pageCount INT
DECLARE @totalCount NVARCHAR(1000)
EXECUTE dbo.PaginationWithOffsetNotInFirstPage 'rowid,[hotelId],[hotelName]', -- varchar(500)
'dbo.TB_HotelList', -- varchar(100)
'hotelId', -- varchar(100)
'asc', -- varchar(50)
'1=1 and cityId=215', -- varchar(100)
3, -- int
20, -- int
5,
@pageCount OUTPUT, -- int
@totalCount OUTPUT
SELECT STR(@pageCount) ,
@totalCount
--不带偏移量的通用 存储过程
CREATE PROC Pagination
@columns VARCHAR(500) , --要显示的列名,用逗号隔开
@tableName VARCHAR(100) , --要查询的表名
@orderColumnName VARCHAR(100) , --排序的列名
@order VARCHAR(50) , --排序的方式,升序为asc,降序为 desc
@where VARCHAR(100) , --where 条件,如果不带查询条件,请用 1=1
@pageIndex INT , --当前页索引
@pageSize INT , --页大小(每页显示的记录条数)
@pageCount INT OUTPUT , --总页数,输出参数
@totalCount INT OUTPUT --总记录数,输出参数
AS
BEGIN
DECLARE @sqlRecordCount NVARCHAR(1000) --得到总记录条数的语句
DECLARE @sqlSelect NVARCHAR(1000) --查询语句
SET @sqlRecordCount = N'select @recordCount=count(*) from '
+ @tableName + ' where ' + @where
DECLARE @recordCount INT --保存总记录条数的变量
EXEC sp_executesql @sqlRecordCount, N'@recordCount int output',
@recordCount OUTPUT
--动态 sql 传参
SET @totalCount = @recordCount --把总记录数赋给输出参数
IF ( @recordCount % @pageSize = 0 ) --如果总记录条数可以被页大小整除
SET @pageCount = @recordCount / @pageSize --总页数就等于总记录条数除以页大小
ELSE --如果总记录条数不能被页大小整除
SET @pageCount = @recordCount / @pageSize + 1 --总页数就等于总记录条数除以页大小加1
SET @sqlSelect = N'select top' + STR(@pageSize) + @columns
+ ' from (
select row_number() over (order by ' + @orderColumnName + ' ' + @order
+ ') as rowId,* from ' + @tableName + ' with(nolock) where ' + @where
+ ') as A where A.rowId >' + STR(( @pageindex - 1 ) * @pageSize)
--STR(( @pageIndex - 1 )* @pageSize + 1) + ' and '
--+ STR(@pageIndex * @pageSize)
EXEC (@sqlSelect) --执行动态Sql
END
go