CREATE PROC sp_PageView
@tbname???? sysname,??????????? --要分頁(yè)顯示的表名
@FieldKey?? nvarchar(1000),????? --用于定位記錄的主鍵(惟一鍵)字段,可以是逗號(hào)分隔的多個(gè)字段
@PageCurrent int=1,????????????? -->0表示要顯示的頁(yè)碼,如果為0表示僅清理緩存數(shù)據(jù)的臨時(shí)表,不返回?cái)?shù)據(jù),其他值代表重建緩存數(shù)據(jù)的臨時(shí)表
@PageSize?? int=10,???????????? --每頁(yè)的大小(記錄數(shù))
@FieldShow? nvarchar(1000)='',??? --以逗號(hào)分隔的要顯示的字段列表,如果不指定,則顯示所有字段
@Where???? nvarchar(1000)='',??? --查詢(xún)條件
@UserName? sysname='',???????? --調(diào)用查詢(xún)的用戶(hù)名
@PageCount? int OUTPUT??????? --總頁(yè)數(shù)
AS
SET NOCOUNT ON
--檢查對(duì)象是否有效
IF OBJECT_ID(@tbname) IS NULL
BEGIN
?RAISERROR(N'對(duì)象"%s"不存在',1,16,@tbname)
?RETURN
END
IF OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTable')=0
?AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsView')=0
?AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTableFunction')=0
BEGIN
?RAISERROR(N'"%s"不是表、視圖或者表值函數(shù)',1,16,@tbname)
?RETURN
END

--分頁(yè)字段檢查
IF ISNULL(@FieldKey,N'')=''
BEGIN
?RAISERROR(N'分頁(yè)處理需要主鍵（或者惟一鍵）',1,16)
?RETURN
END

--其他參數(shù)檢查及規(guī)范
IF ISNULL(@PageSize,0)<1 SET @PageSize=10
IF ISNULL(@FieldShow,N'')=N'' SET @FieldShow=N'*'
IF ISNULL(@Where,N'')=N''
?SET @Where=N''
ELSE
?SET @Where=N'WHERE ( '+@Where+N')'

--分頁(yè)數(shù)據(jù)緩存臨時(shí)表狀態(tài)檢測(cè)
DECLARE @tempTable sysname,@TempField sysname,@TempTableDate datetime
SET @tempTable=QUOTENAME(N'##'
?+RTRIM(LEFT(HOST_NAME(),50))
?+N'_'+RTRIM(LEFT(CASE WHEN ISNULL(@UserName,N'')=N'' THEN SUSER_SNAME() ELSE @UserName END,50))
?+N'_'+RTRIM(@tbname))

SELECT @TempField=QUOTENAME(c.name),
?@TempTableDate=DATEADD(Hour,1,o.crdate) --臨時(shí)表的有效緩存時(shí)間為1小時(shí),創(chuàng)建時(shí)間超過(guò)1小時(shí)的臨時(shí)表會(huì)被重建
FROM tempdb..sysobjects o,tempdb..syscolumns c
WHERE o.id=c.id
?AND o.id=OBJECT_ID( N'tempdb..'+@tempTable )
?AND c.status=0x80
IF @@ROWCOUNT>0
?IF ISNULL(@PageCurrent,0)<1 OR @TempTableDate<GETDATE()
?BEGIN
??EXEC('DROP TABLE '+@tempTable )
??IF @PageCurrent=0 RETURN
?END
?ELSE
??GOTO lb_TempTable_Created
ELSE
?SELECT @TempField=QUOTENAME(NEWID())

--創(chuàng)建分頁(yè)數(shù)據(jù)緩存臨時(shí)表
EXEC(N'SELECT *,IDENTITY(decimal(18,0),0,1) as '+@TempField
?+N' INTO '+@tempTable
?+N' FROM(SELECT TOP 100 PERCENT '+@FieldKey
?+N' FROM '+@tbname
?+N' '+@Where
?+N' ORDER BY NEWID())a')
SET @PageCount=(@@ROWCOUNT+@PageSize-1)/@PageSize
GOTO lb_ShowData

lb_TempTable_Created:
--如果@PageCount為NULL值,則計(jì)算總頁(yè)數(shù)(這樣設(shè)計(jì)可以只在第一次計(jì)算總頁(yè)數(shù),以后調(diào)用時(shí),把總頁(yè)數(shù)傳回給存儲(chǔ)過(guò)程,避免再次計(jì)算總頁(yè)數(shù),對(duì)于不想計(jì)算總頁(yè)數(shù)的處理而言,可以給@PageCount賦值)
IF @PageCount IS NULL
BEGIN
?DECLARE @sql nvarchar(4000)
?SET @sql=N'SELECT @PageCount=COUNT(*)'
??+N' FROM '+@tbname
??+N' '+@Where
?EXEC sp_executesql @sql,N'@PageCount int OUTPUT',@PageCount OUTPUT
?SET @PageCount=(@PageCount+@PageSize-1)/@PageSize
END

lb_ShowData:
IF ISNULL(@PageCurrent,0)<1 SET @PageCurrent=1

--計(jì)算分頁(yè)顯示的TOPN值
DECLARE @TopN varchar(20),@TopN1 varchar(20)
SELECT @TopN=(@PageCurrent-1)*@PageSize,
?@TopN1=@PageCurrent*@PageSize-1

--生成主鍵(惟一鍵)處理?xiàng)l件
DECLARE @Field sysname
SET @Where=N''
WHILE CHARINDEX(N',',@FieldKey)>0
?SELECT @Field=LEFT(@FieldKey,CHARINDEX(N',',@FieldKey)-1),
??@FieldKey=STUFF(@FieldKey,1,CHARINDEX(N',',@FieldKey),N''),
??@Where=@Where
???+N' AND a.'+@Field+N'=b.'+@Field ,
??@FieldShow=REPLACE(@FieldShow,@Field,N'a.'+@Field)
SELECT @Where=STUFF(@Where+N' AND a.'+@FieldKey+N'=b.'+@FieldKey,1,5,N'' ),
?@FieldShow=REPLACE(@FieldShow,@FieldKey,N'a.'+@FieldKey)
IF @FieldShow=N'*' SET @FieldShow=N'a.*'

--執(zhí)行查詢(xún)
EXEC(N'SELECT '+@FieldShow
?+N' FROM '+@tbname
?+N' a,'+@tempTable
?+N'b WHERE ( b.'+@TempField
?+N' BETWEEN '+@TopN
?+N' AND '+@TopN1
?+N') AND ( '+@Where
?+N') ORDER BY b.'+@TempField )

實(shí)現(xiàn)隨機(jī)分頁(yè)的通用分頁(yè)存儲(chǔ)過(guò)程.sql