create proc commonPagination
@columns varchar(500), --要顯示的列名，用逗號(hào)隔開
@tableName varchar(100), --要查詢的表名
@orderColumnName varchar(100), --排序的列名
@order varchar(50), --排序的方式，升序?yàn)閍sc,降序?yàn)?desc
@where varchar(100), --where 條件，如果不帶查詢條件，請(qǐng)用 1=1
@pageIndex int, --當(dāng)前頁索引
@pageSize int, --頁大小(每頁顯示的記錄條數(shù))
@pageCount int output --總頁數(shù)，輸出參數(shù)
as
begin
declare @sqlRecordCount nvarchar(1000) --得到總記錄條數(shù)的語句
declare @sqlSelect nvarchar(1000) --查詢語句
set @sqlRecordCount=N'select @recordCount=count(*) from '
+@tableName + ' where '+ @where
declare @recordCount int --保存總記錄條數(shù)的變量
exec sp_executesql @sqlRecordCount,N'@recordCount int output',@recordCount output
--動(dòng)態(tài) sql 傳參
if( @recordCount % @pageSize = 0) --如果總記錄條數(shù)可以被頁大小整除
set @pageCount = @recordCount / @pageSize --總頁數(shù)就等于總記錄條數(shù)除以頁大小
else --如果總記錄條數(shù)不能被頁大小整除
set @pageCount = @recordCount / @pageSize + 1 --總頁數(shù)就等于總記錄條數(shù)除以頁大小加1
set @sqlSelect =
N'select '+@columns+' from (
select row_number() over (order by '
+@orderColumnName+' '+@order
+') as tempid,* from '
+@tableName+' where '+ @where
+') as tempTableName where tempid between '
+str((@pageIndex - 1)*@pageSize + 1 )
+' and '+str( @pageIndex * @pageSize)
exec (@sqlSelect) --執(zhí)行動(dòng)態(tài)Sql
end
go
--以下是調(diào)用示例
use pubs
go
declare @pageCount int
exec commonPagination
'job_id,job_desc','jobs','job_id',
'asc','1=1',2,2,@pageCount output
select '總頁數(shù)為：' + str(@pageCount)