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)='', -- 關(guān)鍵字段名
@PageSize int = 10, -- 頁尺寸,如果為0則表示返回所有行，不分頁
@PageIndex int = 1, -- 頁碼
@doCount INT OUTPUT, -- 返回記錄總數(shù), 非 0 值則返回
@OrderType bit = 0, -- 設(shè)置排序類型, 非 0 值則降序
@strWhere varchar(2000) = '' , -- 查詢條件 (注意: 不要加 where)
@SortField varchar(500) = '' --排序字段
AS
declare @strSQL varchar(8000) -- 主語句
declare @strTmp varchar(110) -- 臨時(shí)變量
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，就執(zhí)行總數(shù)統(tǒng)計(jì)。以下的所有代碼都是@doCount為0的情況

if len(@SortField) > 0
begin
if @OrderType != 0
begin
set @strOrder = ' order by ' +@SortField+' Desc'
--如果@OrderType不是0，就執(zhí)行降序，這句很重要！
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 != '' ---頁數(shù)為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
--如果是第一頁就執(zhí)行以上代碼，這樣會加快執(zhí)行速度
else
begin
--以下代碼賦予了@strSQL以真正執(zhí)行的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'
-- --表名，所有列，主鍵，條數(shù)，頁碼，是否查總條數(shù)，升降序，條件、按某字段排序

--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