mssql分页存储过程-CSDN博客

USE [mydb]
GO
/****** 对象: StoredProcedure [dbo].[UP_CutPage] 脚本日期: 01/04/2008 16:04:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
------------------------------------
--用途：分页存储过程(对有索引的表效率比较高)
--说明：给出真实的主键数据类型可提运行高效率
--作者：医手
------------------------------------

ALTER PROCEDURE [dbo].[UP_CutPage]
    @tbName      nvarchar(255), -- 表名
    @keyorder      nvarchar(255), -- 主键字段名(或用于排序的字段) '-'结尾为降序 '+'结尾为升序
    @keyorderDataType      nvarchar(255)='sql_variant', -- 主键数据类型(或用于排序的字段数据类型)
    @keymain      nvarchar(255)='', -- 主键字段名(如果用于排序的字段的值不是唯一的，这里需要写真实的主键) '-'结尾为降序 '+'结尾为升序
    @keymainDataType      nvarchar(255)='sql_variant', -- 主键数据类型
@columns nvarchar(1000)='*',
    @strWhere     nvarchar(3000) = '', -- 查询条件 (注意: 不要加 where)
    @PageSize     int = 10, -- 页尺寸
    @PageIndex    int = 1 output, -- 页码
    @RowCount    int=1 output, -- 记录总数,
@PageCount int=1 output
AS
begin

declare @strSQL nvarchar(4000) -- 主语句
declare @strTmp nvarchar(3000) -- 临时变量
declare @strOrder nvarchar(2000) -- 排序类型

--取得总行数
if @strWhere != ''
set @strSQL='SELECT @RowCount=count(*) FROM '+@tbName+' where ' + @strWhere
else
set @strSQL='SELECT @RowCount=count(*) FROM '+@tbName
exec sp_executesql @strSQL, N'@RowCount int out', @RowCount out
set @strSQL=''

--计算总页数
set @PageCount=@RowCount/@PageSize
if (@RowCount % @PageSize<>0)
set @PageCount=@PageCount+1
if (@PageCount<1)
set @PageCount=1

--错误处理
if (@PageSize<1)
set @PageSize=1
if (@PageIndex<1)
set @PageIndex=1
if (@PageIndex>@PageCount)
set @PageIndex=@PageCount

--拼接Order By子句
declare @strOrderType1 nvarchar(1)
declare @strOrderType2 nvarchar(1)
set @strOrderType1=right(@keyorder,1)
if @strOrderType1='+' or @strOrderType1='-'
set @keyorder=left(@keyorder,len(@keyorder)-1)
if @strOrderType1='+'
begin
  set @strOrderType1 = '>'
  set @strOrder = ' order by [' + @keyorder +'] asc'
end
else
begin
  set @strOrderType1 = '<'
  set @strOrder = ' order by [' + @keyorder +'] desc'
end
if @keymain is null
set @keymain=''
if @keymain<>''
begin
set @strOrderType2=right(@keymain,1)
if @strOrderType2='+' or @strOrderType2='-'
  set @keymain=left(@keymain,len(@keymain)-1)
if @strOrderType2='+'
  begin
   set @strOrderType2='>'
   set @strOrder =@strOrder + ', [' + @keymain +'] asc'
  end
else
  begin
   set @strOrderType2='<'
   set @strOrder =@strOrder + ', [' + @keymain +'] desc'
  end
end

--拼接查询语句
if @PageIndex = 1
begin
  set @strTmp =''
  if @strWhere != ''
   set @strTmp = ' where ' + @strWhere
  set @strSQL = 'select top ' + convert(nvarchar(100),@PageSize) + ' '+@columns+' from '
   + @tbName + ' ' + @strTmp + ' ' + @strOrder
end
else
begin
  --求临界值
  if @keyorderDataType is null or @keyorderDataType=''
   set @keyorderDataType='sql_variant'
  if @keymainDataType is null or @keymainDataType=''
   set @keymainDataType='sql_variant'
  set @strSQL='declare @midData '+@keyorderDataType+'; declare @midID '+@keymainDataType+';'
  if @keymain<>''
   set @strSQL =@strSQL+ 'select top ' + convert(nvarchar(100),(@PageIndex-1)*@PageSize) + ' @midData= ['
    + @keyorder + '], @midID=[' + @keymain + '] from ' + @tbName
  else
   set @strSQL =@strSQL+ 'select top ' + convert(nvarchar(100),(@PageIndex-1)*@PageSize) + ' @midData= ['
    + @keyorder + '] from ' + @tbName
  if @strWhere != ''
   set @strSQL=@strSQL+ ' where ' + @strWhere
  set @strSQL=@strSQL+' '+@strOrder+';'
--  print (@strSQL); return
  --最终查询语句
  if @keymain<>''
   set @strSQL =@strSQL+ 'select top ' + convert(nvarchar(100),@PageSize) + ' '+@columns+' from '
    + @tbName + ' where ([' + @keyorder + ']' + @strOrderType1 + '@midData or (['
    + @keyorder + ']=@midData and [' + @keymain + ']' + @strOrderType2 +'@midID))'
  else
   set @strSQL =@strSQL+ 'select top ' + convert(nvarchar(100),@PageSize) + ' '+@columns+' from '
    + @tbName + ' where [' + @keyorder + ']' + @strOrderType1 + '@midData'
  if @strWhere <> ''
   set @strSQL=@strSQL+' and '+ @strWhere
  set @strSQL=@strSQL+' ' + @strOrder
end

--执行查询语句
--print (@strSQL); return
exec (@strSQL)

end

附加一段类似的代码：

-- 查询满足条件的记录集,并获取指定页的数据
CREATE                  PROCEDURE Proc_QueryPages

@Fieldstr NVARCHAR(255) =' * ', -- 查询字段
@Tnamestr NVARCHAR(255) ='', -- 查询表名
@Wherestr NVARCHAR(255) ='', -- 查询条件
@Orderby Nvarchar(255) =' order by id desc ', -- 查询的排序
@PageSize int = 50,          -- 页尺寸
@PageIndex int =1,           -- 页码
@mode int =1,                --返回模式1为数据集,0为数据总数和总页数
@Pagemode int =1,         --是否分页,1为分页,0表示不分页
@CountRs int = 1 output,         ----查询到的记录数
@Countpages int = 1 output             ----查询结果总页数

AS
declare @SQLstr Nvarchar(4000)
declare @Wheretemp1 Nvarchar(1000)
declare @Orderfield1 Nvarchar(1000)
declare @OrderTmp1 Nvarchar(1000)
declare @Orderby1 Nvarchar(1000)

if @mode != 0    --判断结果是统计记录数还是返回数据集,非零返回数据集
BEGIN
if @Pagemode=1 --"1"为分页
    begin
    if @PageIndex=1
        if @Wherestr=''
            set @SQLstr='select top '+ltrim(str(@PageIndex*@PageSize)) +' '+ @Fieldstr + ' from '+ @Tnamestr +' '+@Orderby
        else
                  set @SQLstr='select top '+ltrim(str(@PageIndex*@PageSize)) +' '+ @Fieldstr + ' from '+ @Tnamestr +' where '+ @Wherestr+' '+@Orderby
        --以上代码是查询第一页的,这样可以加速查询
    else
        IF charindex(',',@Orderby)>0
            Begin
                set @SQLstr='SELECT TOP '+ltrim(str(@PageSize))+' '+@Fieldstr
                    +' FROM ' + @Tnamestr + ' WHERE (ID NOT IN (SELECT id FROM (SELECT top '+ltrim(str((@PageIndex-1)*@PageSize))
                    +' * FROM ' + @Tnamestr +@Orderby+') AS t)) '+@Orderby
            End
        ELSE
            Begin
                set @Orderfield1=replace(replace(replace(@Orderby,'order by',''),'desc',''),'asc','')
                ----设置排序的条件字符串
                if charindex('desc', @Orderby)>0
                    set @Wheretemp1=' where '+@Orderfield1+'<(select min('+@Orderfield1+') from (select top '
                else
                    set @Wheretemp1=' where '+@Orderfield1+'>(select max('+@Orderfield1+') from (select top '
                if @Wherestr=''
                    set @SQLstr='select top '+ltrim(str(@PageSize))+' '+@Fieldstr
                    +' from '+@Tnamestr+' '+@wheretemp1
                    +ltrim(str((@PageIndex-1)*@PageSize))
                    +' '+@Orderfield1+' from '+@Tnamestr+' '+@Orderby+') as T ) '+@Orderby
                ELSE
                    set @SQLstr='select top '+ltrim(str(@PageSize))+' '+@Fieldstr
                    +' from '+@Tnamestr+' '+@wheretemp1
                    +ltrim(str((@PageIndex-1)*@PageSize))
                    +' '+@Orderfield1+' from '+@Tnamestr+' where '+@Wherestr+' '+@Orderby+') as T ) AND '
                    +@Wherestr+' '+@Orderby
            End
          end
else --不分页模式代码开始
      begin
    if @Wherestr=''
        set @SQLstr='select ' + @Fieldstr + ' from '+ @Tnamestr +' '+@Orderby
    else
          set @SQLstr='select ' + @Fieldstr + ' from '+ @Tnamestr +' where '+ @Wherestr+' '+@Orderby
      end

exec(@SQLstr)-- 执行查询
--print @SQLstr

END
else    --返回记录数和总页数代码开始
BEGIN
    if @Wherestr=''
        set @SQLstr = 'select @CountRs=count(*) from ' + @Tnamestr
    Else
        set @SQLstr = 'select @CountRs=count(*) from ' + @Tnamestr +' where '+ @Wherestr
        exec sp_executesql @SQLstr,N'@CountRs int out ',@CountRs out

    if @CountRs<=@PageSize
        set @Countpages=1
    else
        begin
            set @Countpages=cast(@CountRs/@PageSize as int)
            if (@CountPages * @PageSize != @CountRs)
            set @Countpages=@CountRs/@PageSize+1
        end
    --print @CountRs
    --print @Countpages
   END

GO