CREATE PROC sp_PageView
@tbname???? sysname,??????????? --要分頁顯示的表名
@FieldKey?? nvarchar(1000),????? --用于定位記錄的主鍵(惟一鍵)字段,可以是逗號分隔的多個字段
@PageCurrent int=1,????????????? -->0表示要顯示的頁碼,如果為0表示僅清理緩存數(shù)據(jù)的臨時表,不返回?cái)?shù)據(jù),其他值代表重建緩存數(shù)據(jù)的臨時表
@PageSize?? int=10,???????????? --每頁的大小(記錄數(shù))
@FieldShow? nvarchar(1000)='',??? --以逗號分隔的要顯示的字段列表,如果不指定,則顯示所有字段
@Where???? nvarchar(1000)='',??? --查詢條件
@UserName? sysname='',???????? --調(diào)用查詢的用戶名
@PageCount? int OUTPUT??????? --總頁數(shù)
AS
SET NOCOUNT ON
--檢查對象是否有效
IF OBJECT_ID(@tbname) IS NULL
BEGIN
?RAISERROR(N'對象"%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

--分頁字段檢查
IF ISNULL(@FieldKey,N'')=''
BEGIN
?RAISERROR(N'分頁處理需要主鍵（或者惟一鍵）',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')'

--分頁數(shù)據(jù)緩存臨時表狀態(tài)檢測
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) --臨時表的有效緩存時間為1小時,創(chuàng)建時間超過1小時的臨時表會被重建
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)建分頁數(shù)據(jù)緩存臨時表
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ì)算總頁數(shù)(這樣設(shè)計(jì)可以只在第一次計(jì)算總頁數(shù),以后調(diào)用時,把總頁數(shù)傳回給存儲過程,避免再次計(jì)算總頁數(shù),對于不想計(jì)算總頁數(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ì)算分頁顯示的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í)行查詢
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ī)分頁的通用分頁存儲過程.sql