CREATE PROC sp_PageView
@tbname     sysname,            --要分頁顯示的表名
@FieldKey   nvarchar(1000),      --用于定位記錄的主鍵(惟一鍵)字段,可以是逗號分隔的多個字段
@PageCurrent int=1,              -->0表示要顯示的頁碼,如果為0表示僅清理緩存數(shù)據(jù)的臨時表,不返回數(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值,則計算總頁數(shù)(這樣設(shè)計可以只在第一次計算總頁數(shù),以后調(diào)用時,把總頁數(shù)傳回給存儲過程,避免再次計算總頁數(shù),對于不想計算總頁數(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

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

--生成主鍵(惟一鍵)處理條件
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)