存储过程中的分页 博客分类： MSSQL SQL Server

create procedure procPageResult
---获得某一页的数据---
@currPage int = 1,   --->当前页页码(即top currPage)
@showColumn varchar(2000) = '*', --->需要查询的字段(即column1,column2,......)
@tabName varchar(2000),   --->需要查看的表名(即from table_name)
@strCondition varchar(2000) = '', --->查询条件(即where condition......) 不用加where关键字
@ascColumn varchar(100) = '',  --->排序的字段名(即order by column asc/desc)
@bitOrderType int = 0,   --->排序的类型(0为升序,1为降序)
@pkColumn varchar(50) = '',  --->主键名称
@pageSize int = 20   --->分页大小
as
begin  --->存储过程开始
---该存储过程需要用到的几个变量---
declare @strTemp varchar(1000)
declare @strSql varchar(4000)  --->该存储过程最后执行的语句
declare @strOrderType varchar(1000) --->排序类型语句(order by column asc/desc)
begin
if @bitOrderType = 1  --->@bitOrderType = 1 即执行降序
begin
    set @strOrderType = ' order by ' + @ascColumn + ' desc'
    set @strTemp = '<(select min'
end
else
begin
    set @strOrderType = ' order by ' + @ascColumn + ' asc'
    set @strTemp = '>(select max'
end
if @currPage = 1 --->如果是第一页
begin
    if @strCondition != ''
set @strSql = 'select top ' + str(@pageSize) + ' ' + @showColumn + ' from ' + @tabName + ' where ' + @strCondition + @strOrderType
     else
set @strSql = 'select top ' + str(@pageSize) + ' ' + @showColumn + ' from ' + @tabName + @strOrderType
end
else  --->其他页
begin
    if @strCondition != ''
set @strSql = 'select top ' + str(@pageSize) + ' ' + @showColumn + ' from ' + @tabName + ' where ' + @strCondition + ' and ' +
@pkColumn + @strTemp + '(' + @pkColumn + ')' + ' from (select top ' + str((@currPage-1)*@pageSize) + ' ' + @pkColumn +
' from ' + @tabName + @strOrderType + ') as TabTemp)' + @strOrderType
    else
set @strSql = 'select top ' + str(@pageSize) + ' ' + @showColumn + ' from ' + @tabName + ' where ' + @pkColumn + @strTemp +
'(' + @pkColumn + ')' + ' from (select top ' + str((@currPage-1)*@pageSize) + ' ' + @pkColumn + ' from ' +
@tabName + @strOrderType + ') as TabTemp)' + @strOrderType
end
end
EXEC (@strSql)
end --->存储过程结束
go