好久沒有上來寫點東西了，今天正好有空，共享一些個人心得，就是關于分頁的存儲過程，這個問題應該是老生重談了，網上的通用存儲過程的類型已經夠多了，但是，好象看到的基本上不能夠滿足一些復雜的SQL語句的分頁（也可能是我不夠見多識廣啊，呵呵），比如下面這句：
select '' as CheckBox, A.TargetID, A.TargetPeriod, Convert(varchar(10), B.BeginDate, 120) as BeginDate,
Convert(varchar(10), B.EndDate, 120) as EndDate, C.SalesCode, C.SalesName, D.CatalogCode, D.CatalogName,
E.OrgID, E.OrgName, F.OrgID as BranchOrgID, F.OrgCode as BranchOrgCode, F.OrgName as BranchOrgName,
A.Amount, '' as DetailButton
from ChlSalesTarget as A
left outer join ChlSalesTargetPeriod as B on A.TargetPeriod=B.TargetPeriod
left outer join ChlSales as C on A.Sales=C.SalesCode
left outer join ChlItemCatalog as D on A.ItemCatalog=D.CatalogCode
left outer join ChlOrg as E on A.OrgID=E.OrgID
left outer join ChlOrg as F on C.BranchOrgID=F.OrgID
where A.TargetPeriod >='200607' and A.TargetPeriod <='200608' and F.OrgCode like '%123%' and E.OrgCode like '%123%'
order by A.TargetPeriod desc,C.SalesName,D.CatalogName上面這句SQL里面有一些特殊情況，比如使用了Convert函數，而且沒有主鍵，有多表連接，有表別名，字段別名等等，這些情況處理起來可能比較棘手，當然，其中的“'' as CheckBox”是我系統當中的特例情況，用來做一些處理的。
我這里提供一個自己開發的通用分頁存儲過程，有什么好的建議和意見，大家請不吝指教。代碼如下：
通用分頁存儲過程----Sp_Paging
/**//*
============================================================
功能： 通用分頁存儲過程
參數：
@PK varchar(50), 主鍵，用來排序的單一字段，空的話，表示沒有主鍵，存儲過程將自動創建標識列主鍵
@Fields varchar(500), 要顯示的字段列表(格式如：ID,Code,Name)
@Tables varchar(1000), 要使用的表集合(Org)
@Where varchar(500), 查詢條件(Code like '100')
@OrderBy varchar(100), 排序條件(支持多個排序字段，如：ID,Code desc,Name desc)
@PageIndex int, 當前要顯示的頁的頁索引，索引從1開始，無記錄時為0。
@PageSize int, 頁大小
創建者：Hollis Yao
創建日期：2006-08-06
備注：
============================================================
*/
Create PROCEDURE [dbo].[Sp_Paging]
@PK varchar(50)='',
@Fields varchar(500),
@Tables varchar(1000),
@Where varchar(500)='',
@OrderBy varchar(100),
@PageIndex int,
@PageSize int
AS
--替換單引號，避免構造SQL出錯
set @Fields = replace(@Fields, '''', '''''')
--要執行的SQL，切分為幾個字符串，避免出現長度超過4k時的問題
declare @SQL1 varchar(4000)
declare @SQL2 varchar(4000)
set @SQL1 = ''
set @SQL2 = ''
if @Where is not null and len(ltrim(rtrim(@Where))) > 0
set @Where = ' where ' + @Where
else
set @Where = ' where 1=1'
set @SQL1 = @SQL1 + ' declare @TotalCount int' --聲明一個變量，總記錄數
set @SQL1 = @SQL1 + ' declare @PageCount int' --聲明一個變量，總頁數
set @SQL1 = @SQL1 + ' declare @PageIndex int' --聲明一個變量，頁索引
set @SQL1 = @SQL1 + ' declare @StartRow int' --聲明一個變量，當前頁第一條記錄的索引
set @SQL1 = @SQL1 + ' select @TotalCount=count(*) from ' + @Tables + @Where --獲取總記錄數
set @SQL1 = @SQL1 + ' if @PageCount <= 0 begin' --如果記錄數為0，直接輸出空的結果集
set @SQL1 = @SQL1 + ' select ' + @Fields + ' from ' + @Tables + ' where 1<>1'
set @SQL1 = @SQL1 + ' select 0 as PageIndex,0 as PageCount,' + convert(varchar, @PageSize) + ' as PageSize,0 as TotalCount'
set @SQL1 = @SQL1 + ' return end'
set @SQL1 = @SQL1 + ' set @PageCount=(@TotalCount+' + convert(varchar, @PageSize) + '-1)/' + convert(varchar, @PageSize) --獲取總頁數
set @SQL1 = @SQL1 + ' set @PageIndex=' + convert(varchar, @PageIndex) --設置正確的頁索引
set @SQL1 = @SQL1 + ' if @PageIndex<0 set @PageIndex=1'
set @SQL1 = @SQL1 + ' if @PageIndex>@PageCount and @PageCount>0 set @PageIndex=@PageCount'
set @SQL1 = @SQL1 + ' set @StartRow=(@PageIndex-1)*' + convert(varchar, @PageSize) + '+1'
if (charindex(',', @OrderBy)=0 and charindex(@PK, @OrderBy)>0)
begin
--****************************************************************************
--****************不需要創建主鍵********************************************
--****************************************************************************
declare @SortDirection varchar(10) --排序方向，>=：升序，<=：倒序
set @SortDirection = '>='
if charindex('desc', @OrderBy) > 0
set @SortDirection = '<='
set @SQL2 = @SQL2 + ' declare @Sort varchar(100)' --聲明一個變量，用來記錄當前頁第一條記錄的排序字段值
set @SQL2 = @SQL2 + ' set rowcount @StartRow' --設置返回記錄數截止到當前頁的第一條
set @SQL2 = @SQL2 + ' select @Sort=' + @PK + ' from ' + @Tables + @Where + ' order by ' + @OrderBy --獲取當前頁第一個排序字段值
set @SQL2 = @SQL2 + ' set rowcount ' + convert(varchar, @PageSize) --設置返回記錄數為頁大小
set @Where = @Where + ' and ' + @PK + @SortDirection + '@Sort'
set @SQL2 = @SQL2 + ' select ' + @Fields + ' from ' + @Tables + @Where + ' order by ' + @OrderBy --輸出最終顯示結果
end
else
begin
--****************************************************************************
--*************需要創建自增長主鍵******************************************
--****************************************************************************
set @SQL2 = @SQL2 + ' declare @EndRow int'
set @SQL2 = @SQL2 + ' set @EndRow=@PageIndex*' + convert(varchar, @PageSize)
set @SQL2 = @SQL2 + ' set rowcount @EndRow'
set @SQL2 = @SQL2 + ' declare @PKBegin int' --聲明一個變量，開始索引
set @SQL2 = @SQL2 + ' declare @PKEnd int' --聲明一個變量，結束索引
set @SQL2 = @SQL2 + ' set @PKBegin=@StartRow'
set @SQL2 = @SQL2 + ' set @PKEnd=@EndRow'
--****************************************************************************
--************對特殊字段進行轉換，以便可以插入到臨時表******************
--****************************************************************************
declare @TempFields varchar(500)
set @TempFields=@Fields
set @TempFields = replace(@TempFields, ''''' as CheckBox', '')
set @TempFields = replace(@TempFields, ''''' as DetailButton', '')
set @TempFields = replace(@TempFields, ''''' as Radio', '')
set @TempFields = LTRIM(RTRIM(@TempFields))
if left(@TempFields,1)=',' --去除最左邊的逗號
set @TempFields = substring(@TempFields, 2, len(@TempFields))
if right(@TempFields,1)=',' --去除最右邊的逗號
set @TempFields = substring(@TempFields, 1, len(@TempFields)-1)
set @SQL2 = @SQL2 + ' select identity(int,1,1) as PK,' + @TempFields + ' into #tb from ' + @Tables + @Where + ' order by ' + @OrderBy
--****************************************************************************
--********去除字段的表名前綴，當有字段有別名時，只保留字段別名*********
--****************************************************************************
declare @TotalFields varchar(500)
declare @tmp varchar(50)
declare @i int
declare @j int
declare @iLeft int --左括號的個數
declare @iRight int --右括號的個數
set @i = 0
set @j = 0
set @iLeft = 0
set @iRight = 0
set @tmp = ''
set @TotalFields = ''
while (len(@Fields)>0)
begin
set @i = charindex(',', @Fields)
--去除字段的表名前綴
if (@i=0)
begin
--找不到逗號分割，即表示只剩下最后一個字段
set @tmp = @Fields
end
else
begin
set @tmp = substring(@Fields, 1, @i)
end
set @j = charindex('.', @tmp)
if (@j>0)
set @tmp = substring(@tmp, @j+1, len(@tmp))
--*******當有字段有別名時，只保留字段別名*********
--帶括號的情況要單獨處理，如Convert(varchar(10), B.EndDate, 120) as EndDate
while (charindex('(', @tmp) > 0)
begin
set @iLeft = @iLeft + 1
set @tmp = substring(@tmp, charindex('(', @tmp)+1, Len(@tmp))
end
while (charindex(')', @tmp) > 0)
begin
set @iRight = @iRight + 1
set @tmp = substring(@tmp, charindex(')', @tmp)+1, Len(@tmp))
end
--當括號恰好組隊的時候，才能進行字段別名的處理
if (@iLeft = @iRight)
begin
set @iLeft = 0
set @iRight = 0
--不對這幾個特殊字段作處理:CheckBox、DetailButton、Radio
if (charindex('CheckBox', @tmp) = 0 and charindex('DetailButton', @tmp) = 0 and charindex('Radio', @tmp) = 0)
begin
--判斷是否有別名
if (charindex('as', @tmp) > 0)--別名的第一種寫法，帶'as'的格式
begin
set @tmp = substring(@tmp, charindex('as', @tmp)+2, len(@tmp))
end
else
begin
if (charindex(' ', @tmp) > 0)--別名的第二種寫法，帶空格(" ")的格式
begin
while(charindex(' ', @tmp) > 0)
begin
set @tmp = substring(@tmp, charindex(' ', @tmp)+1, len(@tmp))
end
end
end
end
set @TotalFields = @TotalFields + @tmp
end
if (@i=0)
set @Fields = ''
else
set @Fields = substring(@Fields, @i+1, len(@Fields))
end
--print @TotalFields
set @SQL2 = @SQL2 + ' select ' + @TotalFields + ' from #tb where PK between @PKBegin and @PKEnd order by PK' --輸出最終顯示結果
set @SQL2 = @SQL2 + ' drop table #tb'
end
--輸出“PageIndex(頁索引)、PageCount(頁數)、PageSize(頁大小)、TotalCount(總記錄數)”
set @SQL2 = @SQL2 + ' select @PageIndex as PageIndex,@PageCount as PageCount,'
+ convert(varchar, @PageSize) + ' as PageSize,@TotalCount as TotalCount'
--print @SQL1 + @SQL2
exec(@SQL1 + @SQL2)
如果使用這個通用分頁存儲過程的話，那么調用方法如下：
使用通用分頁存儲過程進行分頁
/**//*
============================================================
功能： 獲取銷售目標，根據條件
參數：
@UserType int,
@OrgID varchar(500),
@TargetPeriodBegin nvarchar(50),
@TargetPeriodEnd nvarchar(50),
@BranchOrgCode nvarchar(50),
@BranchOrgName nvarchar(50),
@OrgCode nvarchar(50),
@OrgName nvarchar(50),
@SalesCode nvarchar(50),
@SalesName nvarchar(50),
@CatalogCode nvarchar(50),
@CatalogName nvarchar(50),
@PageIndex int, 當前要顯示的頁的頁索引，索引從1開始，無記錄時為0。
@PageSize int, 頁大小
創建者：Hollis Yao
創建日期：2006-08-11
備注：
============================================================
*/
Create PROCEDURE [dbo].[GetSalesTargetList]
@UserType int,
@OrgID nvarchar(500),
@TargetPeriodBegin nvarchar(50),
@TargetPeriodEnd nvarchar(50),
@BranchOrgCode nvarchar(50),
@BranchOrgName nvarchar(50),
@OrgCode nvarchar(50),
@OrgName nvarchar(50),
@SalesCode nvarchar(50),
@SalesName nvarchar(50),
@CatalogCode nvarchar(50),
@CatalogName nvarchar(50),
@PageIndex int,
@PageSize int
AS
declare @Condition nvarchar(2000)
set @Condition = ''
if (@UserType<>1)
set @Condition = @Condition + ' and A.OrgID in (' + @OrgID + ')'
if (len(@TargetPeriodBegin)>0)
set @Condition = @Condition + ' and A.TargetPeriod >=''' + @TargetPeriodBegin + ''''
if (len(@TargetPeriodEnd)>0)
set @Condition = @Condition + ' and A.TargetPeriod <=''' + @TargetPeriodEnd + ''''
if (len(@BranchOrgCode)>0)
set @Condition = @Condition + ' and F.OrgCode like ''%' + @BranchOrgCode + '%'''
if (len(@BranchOrgName)>0)
set @Condition = @Condition + ' and F.OrgName like ''%' + @BranchOrgName + '%'''
if (len(@OrgCode)>0)
set @Condition = @Condition + ' and E.OrgCode like ''%' + @OrgCode + '%'''
if (len(@OrgName)>0)
set @Condition = @Condition + ' and E.OrgName like ''%' + @OrgName + '%'''
if (len(@SalesCode)>0)
set @Condition = @Condition + ' and C.SalesCode like ''%' + @SalesCode + '%'''
if (len(@SalesName)>0)
set @Condition = @Condition + ' and C.SalesName like ''%' + @SalesName + '%'''
if (len(@CatalogCode)>0)
set @Condition = @Condition + ' and D.CatalogCode like ''%' + @CatalogCode + '%'''
if (len(@CatalogName)>0)
set @Condition = @Condition + ' and D.CatalogName like ''%' + @CatalogName + '%'''
if (len(@Condition)>0)
set @Condition = substring(@Condition,5,len(@Condition))
--print @Condition
exec sp_Paging
N'',N''' as CheckBox, A.TargetID, A.TargetPeriod, Convert(varchar(10), B.BeginDate, 120) as BeginDate, Convert(varchar(10), B.EndDate, 120) as EndDate,
C.SalesCode, C.SalesName, D.CatalogCode, D.CatalogName, E.OrgID, E.OrgName, F.OrgID as BranchOrgID, F.OrgCode as BranchOrgCode, F.OrgName as BranchOrgName, A.Amount, '' as DetailButton',
N'ChlSalesTarget as A
left outer join ChlSalesTargetPeriod as B on A.TargetPeriod=B.TargetPeriod
left outer join ChlSales as C on A.Sales=C.SalesCode
left outer join ChlItemCatalog as D on A.ItemCatalog=D.CatalogCode
left outer join ChlOrg as E on A.OrgID=E.OrgID
left outer join ChlOrg as F on C.BranchOrgID=F.OrgID',
@Condition,
N'A.TargetPeriod desc,C.SalesName,D.CatalogName',
@PageIndex, @PageSize
