mssql分页存储过程

本文转自百度文库http://wenku.baidu.com/view/8f6ec149fe4733687e21aa72.html

必须有主键

原代码

use users
go

if exists(select name from sysobjects where name='pages' and xtype='p')
drop proc pages
go

CREATE PROC pages
@tblName varchar(255),		-- 表名
@strGetFields varchar(1000),		-- 需要返回的列 
@fldName varchar(255),		-- 排序的字段名
@PageSize int,				-- 多少条/页
@PageIndex int,				-- 页码
@doCount bit,					-- 返回记录总数, 0不返回,1返回
@OrderType bit,				-- 设置排序类型, 0升序,1降序
@strWhere varchar(1500)		-- 查询条件 (注意: 不要加 where)
AS

declare @strSQL varchar(5000) 	--主语句
declare @strTmp varchar(150)  	--临时变量
declare @strOrder varchar(400)	--排序类型

--如果@doCount传递过来的不是0，就执行总数统计。
if @doCount != 0
	begin
		--查询条件为空
		set @strSQL = 'select count(*) as Total from ' + @tblName
		--查询条件不为空
		if @strWhere !=''
			set @strSQL = 'select count(*) as Total from ' + @tblName + ' where '+@strWhere
	end 

--以下的所有代码都是@doCount为0的情况：
--如果@OrderType是1，就执行降序，否则为升序！
else
	begin
		--查询条件为空
		set @strTmp = '>(select max'
		set @strOrder = ' order by ' + @fldName +' asc'
		--查询条件不为空
		if @OrderType = 1
			begin
				set @strTmp = '<(select min'
				set @strOrder = ' order by ' + @fldName +' desc'
			end

		--如果是第一页就执行代码，这样会加快执行速度
		if @PageIndex = 1
			begin
				--查询条件为空
				set @strSQL='select top '+str(@PageSize)+''+@strGetFields+' from '+@tblName+''+@strOrder
				--如果查询条件不为空，
				if @strWhere != '' 
					set @strSQL='select top '+str(@PageSize)+''+@strGetFields+' from '+@tblName+' where '+@strWhere+''+@strOrder
			end
		--如果不是第一页，则
		else
			begin
			--以下代码赋予了@strSQL以真正执行的SQL代码

				--sql查询语句
				set @strSQL = 'select top ' + str(@PageSize) +''+@strGetFields+ ' from '
						+ @tblName + ' where ' + @fldName + '' + @strTmp + '('
						+ @fldName + ') from (select top ' + str((@PageIndex-1)*@PageSize) + ''
						+ @fldName + ' from ' + @tblName + '' + @strOrder + ') as tblTmp)'+ @strOrder


				--如果查询条件不为空，则另写sql语句
				if @strWhere != ''
					set @strSQL = 'select top ' + str(@PageSize) +''+@strGetFields+ ' from '
							+ @tblName + ' where ' + @fldName + '' + @strTmp + '('
							+ @fldName + ') from (select top ' + str((@PageIndex-1)*@PageSize) + ''
							+ @fldName + ' from ' + @tblName + ' where ' + @strWhere + ''
							+ @strOrder + ') as tblTmp) and ' + @strWhere + '' + @strOrder
			end 
	end 
exec (@strSQL)
GO

--运行存储过程pages
--表名varchar,要返回的列名(*为返回所有)varchar,排序的列varchar,每页显示数量int,
--第几页int,是否统计总数bit,是否降序排列bit,排列条件(不要带where)varchar

--统计borrow表的记录数量
exec pages 'uu','*','',0,0,1,0,''
--按borrowid排倒序分别显示1、2、3页的信息
exec pages 'uu','*','uid',4,1,0,1,''
exec pages 'uu','*','uid',4,2,0,1,'uid is not null'
exec pages 'uu','*','uid',4,3,0,1,''

 简单改造，调整了参数的顺序，重新进行了命名，以符合的我的使用习惯

if exists(select name from sysobjects where name='pages' and xtype='p')
drop proc pages
go

CREATE PROC pages
@bit_DoCount BIT   ,         -- 返回记录总数, 0不返回,1返回
@str_tblName varchar(255),      -- 表名
@str_SelectFields varchar(1000),  -- 需要返回的列(所有字段*)
@str_Where varchar(1500),          -- 查询条件 (注意: 不要加 where)
@str_PkField varchar(255),        --主键名称(决定着取出来的记录)
@bit_OrderType bit,               -- 设置排序类型, 0升序,1降序
@int_PageSize int,                  -- 多少条/页
@int_PageIndex int                 -- 页码
AS

declare @strSQL varchar(5000)     --主语句
declare @strTmp varchar(150)      --临时变量
declare @strOrder varchar(400)    --排序类型

--如果@doCount传递过来的不是0，就执行总数统计。
if @bit_DoCount != 0
    begin
        --查询条件为空
        set @strSQL = 'select count(*) as Total from ' + @str_tblName
        --查询条件不为空
        if @str_Where !=''
            set @strSQL = 'select count(*) as Total from ' + @str_tblName + ' where '+@str_Where
    end 

--以下的所有代码都是@doCount为0的情况：
--如果@OrderType是1，就执行降序，否则为升序！
else
    begin
        --查询条件为空
        set @strTmp = '>(select max'
        set @strOrder = ' order by ' + @str_PkField +' asc'
        --查询条件不为空
        if @bit_OrderType = 1
            begin
                set @strTmp = '<(select min'
                set @strOrder = ' order by ' + @str_PkField +' desc'
            end

        --如果是第一页就执行代码，这样会加快执行速度
        if @int_PageIndex = 1
            begin
                --查询条件为空
                set @strSQL='select top '+str(@int_PageSize)+' '+@str_SelectFields+' from '+@str_tblName+' '+@strOrder
                --如果查询条件不为空，
                if @str_Where != '' 
                    set @strSQL='select top '+str(@int_PageSize)+' '+@str_SelectFields+' from '+@str_tblName+' where '+@str_Where+' '+@strOrder
            end
        --如果不是第一页，则
        else
            begin
            --以下代码赋予了@strSQL以真正执行的SQL代码

                --sql查询语句
                set @strSQL = 'select top ' + str(@int_PageSize) +' '+@str_SelectFields+ ' from '
                        + @str_tblName + ' where ' + @str_PkField + '' + @strTmp + '('
                        + @str_PkField + ') from (select top ' + str((@int_PageIndex-1)*@int_PageSize) + ' '
                        + @str_PkField + ' from ' + @str_tblName + '' + @strOrder + ') as tblTmp)'+ @strOrder


                --如果查询条件不为空，则另写sql语句
                if @str_Where != ''
                    set @strSQL = 'select top ' + str(@int_PageSize) +' '+@str_SelectFields+ ' from '
                            + @str_tblName + ' where ' + @str_PkField + '' + @strTmp + '('
                            + @str_PkField + ') from (select top ' + str((@int_PageIndex-1)*@int_PageSize) + ' '
                            + @str_PkField + ' from ' + @str_tblName + ' where ' + @str_Where + ' '
                            + @strOrder + ') as tblTmp) and ' + @str_Where + ' ' + @strOrder
            end 
    end 
    PRINT @strSQL 
exec (@strSQL)
GO