SqlServer通用的分页存储过程

CREATE PROCEDURE PrcPager
-- 获得某一页的数据 -- 
@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 bit = 0,---排序的类型 (0为升序,1为降序) 
@pkColumn varchar(50) = '',--主键名称 
@pageSize int = 20                               --分页大小 
AS 
BEGIN -- 存储过程开始 
-- 该存储过程需要用到的几个变量 
DECLARE @strSql varchar(4000)           --该存储过程最后执行的语句 
DECLARE @strOrderType varchar(1000)     --排序类型语句 (order by column asc或者order by column desc) 
BEGIN 
IF @bitOrderType = 1   -- bitOrderType=1即执行降序 
BEGIN 
    SET @strOrderType = ' ORDER BY '+@ascColumn+' DESC' 
END 
ELSE 
BEGIN 
    SET @strOrderType = ' ORDER BY '+@ascColumn+' ASC' 
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+' not in (SELECT TOP '+STR((@currPage-1)*@pageSize)+ 
        --' '+@pkColumn+' FROM '+@tabName+' WHERE '+@strCondition+@strOrderType+')'+@strOrderType 
 SET @strSql ='select top '+str(@pageSize)+' '+@showColumn+' from (select top '+str(@currPage*@pageSize)+@showColumn+' from '
 +@tabName+' WHERE ('+@strCondition+') '+@strOrderType+') as temp  where '+@pkColumn+' not in(SELECT TOP '+STR((@currPage-1)*@pageSize)
 +' '+@pkColumn+ ' FROM '+@tabName+' WHERE ('+@strCondition+') ' +@strOrderType+')'
    ELSE 
        --SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@showColumn+' FROM '+@tabName+ 
        --' WHERE '+@pkColumn+' not in(SELECT TOP '+STR((@currPage-1)*@pageSize)+' '+@pkColumn+ 
        --' FROM '+@tabName+@strOrderType+')'+@strOrderType
 SET @strSql ='select top '+str(@pageSize)+' '+@showColumn+' from (select top '+str(@currPage*@pageSize)+@showColumn+' from '+@tabName+@strOrderType+') as temp'
 +' where '+@pkColumn+' not in(SELECT TOP '+STR((@currPage-1)*@pageSize)+' '+@pkColumn+ ' FROM '+@tabName+@strOrderType+')'
END 
END 
EXEC (@strSql)
print @strSql
END  -- 存储过程结束


GO

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!