sqlserver 通用分頁存儲過程,用存儲過程可以提高效率與節約時間。
代碼如下:
create proc commonPagination
@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 –總頁數，輸出參數
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 傳參
if( @recordCount % @pageSize = 0) –如果總記錄條數可以被頁大小整除
set @pageCount = @recordCount / @pageSize –總頁數就等于總記錄條數除以頁大小
else –如果總記錄條數不能被頁大小整除
set @pageCount = @recordCount / @pageSize + 1 –總頁數就等于總記錄條數除以頁大小加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) –執行動態Sql
end
go
–以下是調用示例
use pubs
go
declare @pageCount int
exec commonPagination
‘job_id,job_desc’,’jobs’,’job_id’,
‘asc’,’1=1′,2,2,@pageCount output
select ‘總頁數為：’ + str(@pageCount)
