/* --用存儲過程實現的分頁程序
顯示指定表、視圖、查詢結果的第X頁
對于表中主鍵或標識列的情況,直接從原表取數查詢，其它情況使用臨時表的方法
如果視圖或查詢結果中有主鍵,不推薦此方法

--鄒建 2003.09(引用請保留此信息)-- */

/* --調用示例
exec p_show '地區資料'

exec p_show '地區資料',5,3,'地區編號,地區名稱,助記碼','地區編號'
-- */

if exists ( select * from dbo.sysobjects where id = object_id (N ' [dbo].[p_show] ' )
and OBJECTPROPERTY (id, N ' IsProcedure ' ) = 1 )
drop procedure [ dbo ] . [ p_show ]
GO

CREATE Proc p_show
@QueryStr nvarchar ( 4000 ), -- 表名、視圖名、查詢語句
@PageSize int = 10 ,?? -- 每頁的大小(行數)
@PageCurrent int = 1 ,?? -- 要顯示的頁
@FdShow nvarchar ( 4000 ) = '' , -- 要顯示的字段列表,如果查詢結果有標識字段,需要指定此值,且不包含標識字段
@FdOrder nvarchar ( 1000 ) = '' -- 排序字段列表
as
declare @FdName nvarchar ( 250 ) -- 表中的主鍵或表、臨時表中的標識列名
, @Id1 varchar ( 20 ), @Id2 varchar ( 20 ) -- 開始和結束的記錄號
, @Obj_ID int ??? -- 對象ID
-- 表中有復合主鍵的處理
declare @strfd nvarchar ( 2000 ) -- 復合主鍵列表
, @strjoin nvarchar ( 4000 ) -- 連接字段
, @strwhere nvarchar ( 2000 ) -- 查詢條件


select @Obj_ID = object_id ( @QueryStr )
, @FdShow = case isnull ( @FdShow , '' ) when '' then ' * ' else ' ' + @FdShow end
, @FdOrder = case isnull ( @FdOrder , '' ) when '' then '' else ' order by ' + @FdOrder end
, @QueryStr = case when @Obj_ID is not null then ' ' + @QueryStr else ' ( ' + @QueryStr + ' ) a ' end

-- 如果顯示第一頁，可以直接用top來完成
if @PageCurrent = 1
begin
select @Id1 = cast ( @PageSize as varchar ( 20 ))
exec ( ' select top ' + @Id1 + @FdShow + ' from ' + @QueryStr + @FdOrder )
return
end

-- 如果是表,則檢查表中是否有標識更或主鍵
if @Obj_ID is not null and objectproperty ( @Obj_ID , ' IsTable ' ) = 1
begin
select @Id1 = cast ( @PageSize as varchar ( 20 ))
? , @Id2 = cast (( @PageCurrent - 1 ) * @PageSize as varchar ( 20 ))

select @FdName = name from syscolumns where id = @Obj_ID and status = 0x80
if @@rowcount = 0 ?? -- 如果表中無標識列,則檢查表中是否有主鍵
begin
? if not exists ( select 1 from sysobjects where parent_obj = @Obj_ID and xtype = ' PK ' )
?? goto lbusetemp? -- 如果表中無主鍵,則用臨時表處理

? select @FdName = name from syscolumns where id = @Obj_ID and colid in (
?? select colid from sysindexkeys where @Obj_ID = id and indid in (
??? select indid from sysindexes where @Obj_ID = id and name in (
???? select name from sysobjects where xtype = ' PK ' and parent_obj = @Obj_ID
?? )))
? if @@rowcount > 1 ? -- 檢查表中的主鍵是否為復合主鍵
? begin
?? select @strfd = '' , @strjoin = '' , @strwhere = ''
?? select @strfd = @strfd + ' ,[ ' + name + ' ] '
??? , @strjoin = @strjoin + ' and a.[ ' + name + ' ]=b.[ ' + name + ' ] '
??? , @strwhere = @strwhere + ' and b.[ ' + name + ' ] is null '
??? from syscolumns where id = @Obj_ID and colid in (
??? select colid from sysindexkeys where @Obj_ID = id and indid in (
???? select indid from sysindexes where @Obj_ID = id and name in (
????? select name from sysobjects where xtype = ' PK ' and parent_obj = @Obj_ID
??? )))
?? select @strfd = substring ( @strfd , 2 , 2000 )
??? , @strjoin = substring ( @strjoin , 5 , 4000 )
??? , @strwhere = substring ( @strwhere , 5 , 4000 )
?? goto lbusepk
? end
end
end
else
goto lbusetemp

/* --使用標識列或主鍵為單一字段的處理方法-- */
lbuseidentity:
exec ( ' select top ' + @Id1 + @FdShow + ' from ' + @QueryStr
? + ' where ' + @FdName + ' not in(select top '
? + @Id2 + ' ' + @FdName + ' from ' + @QueryStr + @FdOrder
? + ' ) ' + @FdOrder
? )
return

/* --表中有復合主鍵的處理方法-- */
lbusepk:?
exec ( ' select ' + @FdShow + ' from(select top ' + @Id1 + ' a.* from
? (select top 100 percent * from ' + @QueryStr + @FdOrder + ' ) a
? left join (select top ' + @Id2 + ' ' + @strfd + '
? from ' + @QueryStr + @FdOrder + ' ) b on ' + @strjoin + '
? where ' + @strwhere + ' ) a '
? )
return

/* --用臨時表處理的方法-- */
lbusetemp:?
select @FdName = ' [ID_ ' + cast ( newid () as varchar ( 40 )) + ' ] '
, @Id1 = cast ( @PageSize * ( @PageCurrent - 1 ) as varchar ( 20 ))
, @Id2 = cast ( @PageSize * @PageCurrent - 1 as varchar ( 20 ))

exec ( ' select ' + @FdName + ' =identity(int,0,1), ' + @FdShow + '
? into #tb from ' + @QueryStr + @FdOrder + '
select ' + @FdShow + ' from #tb where ' + @FdName + ' between '
+ @Id1 + ' and ' + @Id2
)
GO
=============================================================

-- TOP n 實現的通用分頁存儲過程(轉自鄒建)
CREATE PROC sp_PageView
@tbname ???? sysname,?????????????? -- 要分頁顯示的表名
@FieldKey ?? nvarchar ( 1000 ),????? -- 用于定位記錄的主鍵(惟一鍵)字段,可以是逗號分隔的多個字段
@PageCurrent int = 1 ,?????????????? -- 要顯示的頁碼
@PageSize ?? int = 10 ,??????????????? -- 每頁的大小(記錄數)
@FieldShow nvarchar ( 1000 ) = '' ,????? -- 以逗號分隔的要顯示的字段列表,如果不指定,則顯示所有字段
@FieldOrder nvarchar ( 1000 ) = '' ,????? -- 以逗號分隔的排序字段列表,可以指定在字段后面指定DESC/ASC
????????????????????????????????????????? 用于指定排序順序
@Where ??? nvarchar ( 1000 ) = '' ,???? -- 查詢條件
@PageCount int OUTPUT???????????? -- 總頁數
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"不是表、視圖或者表值函數 ' , 1 , 16 , @tbname )
??? RETURN
END

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

-- 其他參數檢查及規范
IF ISNULL ( @PageCurrent , 0 ) < 1 SET @PageCurrent = 1
IF ISNULL ( @PageSize , 0 ) < 1 SET @PageSize = 10
IF ISNULL ( @FieldShow ,N '' ) = N '' SET @FieldShow = N ' * '
IF ISNULL ( @FieldOrder ,N '' ) = N ''
??? SET @FieldOrder = N ''
ELSE
??? SET @FieldOrder = N ' ORDER BY ' + LTRIM ( @FieldOrder )
IF ISNULL ( @Where ,N '' ) = N ''
??? SET @Where = N ''
ELSE
??? SET @Where = N ' WHERE ( ' + @Where + N ' ) '

-- 如果@PageCount為NULL值,則計算總頁數(這樣設計可以只在第一次計算總頁數,以后調用時,把總頁數傳回給存儲過程,避免再次計算總頁數,對于不想計算總頁數的處理而言,可以給@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

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

-- 第一頁直接顯示
IF @PageCurrent = 1
??? EXEC (N ' SELECT TOP ' + @TopN
??????? + N ' ' + @FieldShow
??????? + N ' FROM ' + @tbname
??????? + N ' ' + @Where
??????? + N ' ' + @FieldOrder )
ELSE
BEGIN
??? -- 處理別名
??? IF @FieldShow = N ' * '
??????? SET @FieldShow = N ' a.* '

??? -- 生成主鍵(惟一鍵)處理條件
??? DECLARE @Where1 nvarchar ( 4000 ), @Where2 nvarchar ( 4000 ),
??????? @s nvarchar ( 1000 ), @Field sysname
??? SELECT @Where1 = N '' , @Where2 = N '' , @s = @FieldKey
??? WHILE CHARINDEX (N ' , ' , @s ) > 0
??????? SELECT @Field =LEFT ( @s , CHARINDEX (N ' , ' , @s ) - 1 ),
??????????? @s = STUFF ( @s , 1 , CHARINDEX (N ' , ' , @s ),N '' ),
??????????? @Where1 = @Where1 + N ' AND a. ' + @Field + N ' =b. ' + @Field ,
??????????? @Where2 = @Where2 + N ' AND b. ' + @Field + N ' IS NULL ' ,
??????????? @Where = REPLACE ( @Where , @Field ,N ' a. ' + @Field ),
??????????? @FieldOrder = REPLACE ( @FieldOrder , @Field ,N ' a. ' + @Field ),
??????????? @FieldShow = REPLACE ( @FieldShow , @Field ,N ' a. ' + @Field )
??? SELECT @Where = REPLACE ( @Where , @s ,N ' a. ' + @s ),
??????? @FieldOrder = REPLACE ( @FieldOrder , @s ,N ' a. ' + @s ),
??????? @FieldShow = REPLACE ( @FieldShow , @s ,N ' a. ' + @s ),
??????? @Where1 = STUFF ( @Where1 + N ' AND a. ' + @s + N ' =b. ' + @s , 1 , 5 ,N '' ),???
??????? @Where2 = CASE
??????????? WHEN @Where = '' THEN N ' WHERE ( '
??????????? ELSE @Where + N ' AND ( '
??????????? END + N ' b. ' + @s + N ' IS NULL ' + @Where2 + N ' ) '

??? -- 執行查詢
??? EXEC (N ' SELECT TOP ' + @TopN
??????? + N ' ' + @FieldShow
??????? + N ' FROM ' + @tbname
??????? + N ' a LEFT JOIN(SELECT TOP ' + @TopN1
??????? + N ' ' + @FieldKey
??????? + N ' FROM ' + @tbname
??????? + N ' a ' + @Where
??????? + N ' ' + @FieldOrder
??????? + N ' )b ON ' + @Where1
??????? + N ' ' + @Where2
??????? + N ' ' + @FieldOrder )
END

=============================================================
-- 字符串緩存實現的通用分頁存儲過程(轉自鄒建)
CREATE PROC sp_PageView
@tbname ???? sysname,?????????????? -- 要分頁顯示的表名
@FieldKey ?? sysname,?????????????? -- 用于定位記錄的主鍵(惟一鍵)字段,只能是單個字段
@PageCurrent int = 1 ,???????????????? -- 要顯示的頁碼
@PageSize ?? int = 10 ,??????????????? -- 每頁的大小(記錄數)
@FieldShow ? nvarchar ( 1000 ) = '' ,????? -- 以逗號分隔的要顯示的字段列表,如果不指定,則顯示所有字段
@FieldOrder ? nvarchar ( 1000 ) = '' ,???? -- 以逗號分隔的排序字段列表,可以指定在字段后面指定DESC/ASC
????????????????????????????????????????? 用于指定排序順序
@Where ???? nvarchar ( 1000 ) = '' ,????? -- 查詢條件
@PageCount ? int OUTPUT??????????? -- 總頁數
AS
DECLARE @sql nvarchar ( 4000 )
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"不是表、視圖或者表值函數 ' , 1 , 16 , @tbname )
??? RETURN
END

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

-- 其他參數檢查及規范
IF ISNULL ( @PageCurrent , 0 ) < 1 SET @PageCurrent = 1
IF ISNULL ( @PageSize , 0 ) < 1 SET @PageSize = 10
IF ISNULL ( @FieldShow ,N '' ) = N '' SET @FieldShow = N ' * '
IF ISNULL ( @FieldOrder ,N '' ) = N ''
??? SET @FieldOrder = N ''
ELSE
??? SET @FieldOrder = N ' ORDER BY ' + LTRIM ( @FieldOrder )
IF ISNULL ( @Where ,N '' ) = N ''
??? SET @Where = N ''
ELSE
??? SET @Where = N ' WHERE ( ' + @Where + N ' ) '

-- 如果@PageCount為NULL值,則計算總頁數(這樣設計可以只在第一次計算總頁數,以后調用時,把總頁數傳回給存儲過程,避免再次計算總頁數,對于不想計算總頁數的處理而言,可以給@PageCount賦值)
IF @PageCount IS NULL
BEGIN
??? 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

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

-- 第一頁直接顯示
IF @PageCurrent = 1
??? EXEC (N ' SELECT TOP ' + @TopN
??????? + N ' ' + @FieldShow
??????? + N ' FROM ' + @tbname
??????? + N ' ' + @Where
??????? + N ' ' + @FieldOrder )
ELSE
BEGIN
??? SELECT @PageCurrent = @TopN1 ,
??????? @sql = N ' SELECT @n=@n-1,@s=CASE WHEN @n< ' + @TopN
??????????? + N ' THEN @s+N '' , '' +QUOTENAME(RTRIM(CAST( ' + @FieldKey
??????????? + N ' as varchar(8000))),N '''''''' ) ELSE N '''' END FROM ' + @tbname
??????????? + N ' ' + @Where
??????????? + N ' ' + @FieldOrder
??? SET ROWCOUNT @PageCurrent
??? EXEC sp_executesql @sql ,
??????? N ' @n int,@s nvarchar(4000) OUTPUT ' ,
??????? @PageCurrent , @sql OUTPUT
??? SET ROWCOUNT 0
??? IF @sql = N ''
??????? EXEC (N ' SELECT TOP 0 '
??????????? + N ' ' + @FieldShow
??????????? + N ' FROM ' + @tbname )
??? ELSE
??? BEGIN
??????? SET @sql = STUFF ( @sql , 1 , 1 ,N '' )???????
??????? -- 執行查詢
??????? EXEC (N ' SELECT TOP ' + @TopN
??????????? + N ' ' + @FieldShow
??????????? + N ' FROM ' + @tbname
??????????? + N ' WHERE ' + @FieldKey
??????????? + N ' IN( ' + @sql
??????????? + N ' ) ' + @FieldOrder )
??? END
END

=============================================================
-- 臨時表緩存實現的通用分頁存儲過程
CREATE PROC sp_PageView
@tbname sysname, --要分頁顯示的表名
@FieldKey nvarchar(1000), --用于定位記錄的主鍵(惟一鍵)字段,可以是逗號分隔的多個字段
@PageCurrent int=1, --要顯示的頁碼
@PageSize int=10, --每頁的大小(記錄數)
@FieldShow nvarchar(1000)='', --以逗號分隔的要顯示的字段列表,如果不指定,則顯示所有字段
@FieldOrder nvarchar(1000)='', --以逗號分隔的排序字段列表,可以指定在字段后面指定DESC/ASC用于指定排序順序
@Where nvarchar(1000)='', --查詢條件
@PageCount int OUTPUT --總頁數
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"不是表、視圖或者表值函數',1,16,@tbname)
RETURN
END

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

--其他參數檢查及規范
IF ISNULL(@PageCurrent,0)<1 SET @PageCurrent=1
IF ISNULL(@PageSize,0)<1 SET @PageSize=10
IF ISNULL(@FieldShow,N'')=N'' SET @FieldShow=N'*'
IF ISNULL(@FieldOrder,N'')=N''
SET @FieldOrder=N''
ELSE
SET @FieldOrder=N'ORDER BY '+LTRIM(@FieldOrder)
IF ISNULL(@Where,N'')=N''
SET @Where=N''
ELSE
SET @Where=N'WHERE ('+@Where+N')'

--如果@PageCount為NULL值,則計算總頁數(這樣設計可以只在第一次計算總頁數,以后調用時,把總頁數傳回給存儲過程,避免再次計算總頁數,對于不想計算總頁數的處理而言,可以給@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

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

--第一頁直接顯示
IF @PageCurrent=1
EXEC(N'SELECT TOP '+@TopN
+N' '+@FieldShow
+N' FROM '+@tbname
+N' '+@Where
+N' '+@FieldOrder)
ELSE
BEGIN
--生成主鍵(惟一鍵)處理條件
DECLARE @Where1 nvarchar(4000),@s nvarchar(1000)
SELECT @Where1=N'',@s=@FieldKey
WHILE CHARINDEX(N',',@s)>0
SELECT @s=STUFF(@s,1,CHARINDEX(N',',@s),N''),
@Where1=@Where1
+N' AND a.'+LEFT(@s,CHARINDEX(N',',@s)-1)
+N'='+LEFT(@s,CHARINDEX(N',',@s)-1)
SELECT @Where1=STUFF(@Where1+N' AND a.'+@s+N'='+@s,1,5,N''),
@TopN=@TopN1-@PageSize

--執行查詢
EXEC(N'SET ROWCOUNT '+@TopN1
+N' SELECT '+@FieldKey
+N' INTO # FROM '+@tbname
+N' '+@Where
+N' '+@FieldOrder
+N' SET ROWCOUNT '+@TopN
+N' DELETE FROM #'
+N' SELECT '+@FieldShow
+N' FROM '+@tbname
+N' a WHERE EXISTS(SELECT * FROM # WHERE '+@Where1
+N') '+@FieldOrder)
END

=============================================================

?

【摘】CSDN論壇討論帖：用存儲過程實現的分頁程序