MSSQL分页存储过程

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

if exists(select * from sysobjects where name='DataPaging')
drop proc DataPaging
go

create PROCEDURE [dbo].[DataPaging]
@tblName varchar(255), -- 表名
@strGetFields varchar(1000) = '*', -- 需要返回的列
@fldName varchar(255)='', -- 关键字段名
@PageSize int = 10, -- 页尺寸,如果为0则表示返回所有行，不分页
@PageIndex int = 1, -- 页码
@doCount INT OUTPUT, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(2000) = '' , -- 查询条件 (注意: 不要加 where)
@SortField varchar(500) = '' --排序字段
AS
declare @strSQL varchar(8000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
declare @str nvarchar(4000)
if @doCount != 0
begin
if @strWhere !=''
set @str = N'select @doCount=count(*) from [' + Convert(nvarchar(255),@tblName) + N'] with (nolock) where 1=1 '+Convert(nvarchar(2000),@strWhere)
else
set @str = N'select @doCount=count(*) from [' + Convert(nvarchar(255),@tblName) + N'] with (nolock) '

execute sp_executesql @str, N'@doCount INT output', @doCount output

print @doCount
end

--以上代码的意思是如果@doCount传递过来的不是0，就执行总数统计。以下的所有代码都是@doCount为0的情况

if len(@SortField) > 0
begin
if @OrderType != 0
begin
set @strOrder = ' order by ' +@SortField+' Desc'
--如果@OrderType不是0，就执行降序，这句很重要！
end
else
begin
set @strOrder = ' order by ' +@SortField
end
end
else
begin
set @strOrder=''
end
if @PageSize=0
begin
if @strWhere != ''
set @strSQL = 'select '+@strGetFields+ ' from [' + @tblName + '] with (nolock) where 1=1 ' + @strWhere + ' ' + @strOrder
else
set @strSQL = 'select '+@strGetFields+ ' from ['+ @tblName + '] with (nolock) '+ @strOrder
end

else
begin
if @PageIndex = 1
begin
if @strWhere != '' ---页数为1 ，条件不为空
set @strSQL = 'select * from (select top('+convert(varchar(20),@PageSize)+')
'+@strGetFields+' from '+@tblName+' where 1=1 '+@strWhere+' order by '+@fldName+') TB '+@strOrder
else
set @strSQL = 'select * from (select top('+convert(varchar(20),@PageSize)+') '+@strGetFields+' from '+@tblName+' order by '+@fldName+') TB '+@strOrder
end
--如果是第一页就执行以上代码，这样会加快执行速度
else
begin
--以下代码赋予了@strSQL以真正执行的SQL代码

if @strWhere=''
set @strSQL = 'select * from (select top('+convert(varchar(20),@PageSize)+') '+@strGetFields+' from '+@tblName+' where 1=1 and '+
@fldName+' > (select max('+@fldName+') from (select top ('+convert(varchar(20),@PageSize*(@PageIndex-1))+') '+@fldName+' from '+@tblName
+' order by '+@fldName+' ) T ) order by '+@fldName+') TB '+@strOrder
else
set @strSQL = 'select * from (select top('+convert(varchar(20),@PageSize)+') '+@strGetFields+' from '+@tblName+' where 1=1 '+
@strWhere+' and '+@fldName+' > (select max('+@fldName+') from (select top ('+convert(varchar(20),@PageSize*(@PageIndex-1))+') '+@fldName+
' from '+@tblName+' where 1=1 '+@strWhere+' order by '+@fldName+' ) T )'+' order by '+@fldName+') TB '
+ ' where 1=1 ' + @strWhere + ' ' + @strOrder
end
end

print @strSQL
exec (@strSQL)

--exec DataPaging 'Sys_User','*','UserID',5,2,1,1, '','UserID'
-- --表名，所有列，主键，条数，页码，是否查总条数，升降序，条件、按某字段排序

--select * from (select top 30 * from View_Accounts where 1=1 and AccountsID > (select max(AccountsID) from (select top 30 AccountsID
-- from View_Accounts where 1=1 order by AccountsID ) T ) order by AccountsID) TB
-- where 1=1 order by AccountsID