
/**//**//**//*
说明:1.支持多表查询 2.支持任意排序 3.不支持表别名
参考了
evafly920:[分享]千万数量级分页存储过程(效果演示)
地址:http://blog.csdn.net/evafly920/archive/2006/03/03/614813.aspx

IF(EXISTS(SELECT * FROM sysobjects WHERE [id]=OBJECT_ID('usp_PagingLarge') AND xtype='P'))
DROP PROCEDURE usp_PagingLarge
*/
GO
CREATE PROCEDURE usp_PagingLarge
@TableNames VARCHAR(
200),
--表名,可以是多个表,但不能用别名
@PrimaryKey VARCHAR(
100),
--主键,可以为空,但@Order为空时该值不能为空
@Fields VARCHAR(
200),
--要取出的字段,可以是多个表的字段,可以为空,为空表示select *
@PageSize INT,
--每页记录数
@CurrentPage INT,
--当前页,0表示第1页
@Filter VARCHAR(
200)
= '',
--条件,可以为空,不用填 where
@Group VARCHAR(
200)
= '',
--分组依据,可以为空,不用填 group by
@Order VARCHAR(
200)
= '' --排序,可以为空,为空默认按主键升序排列,不用填 order by
AS
BEGIN
DECLARE @SortColumn VARCHAR(
200)
DECLARE @Operator CHAR(
2)
DECLARE @SortTable VARCHAR(
200)
DECLARE @SortName VARCHAR(
200)
IF @Fields = ''
SET @Fields = '*'
IF @Filter = ''
SET @Filter = 'WHERE 1=1'
ELSE
SET @Filter = 'WHERE ' + @Filter
IF @Group <>''
SET @Group = 'GROUP BY ' + @Group
IF @Order <> ''
BEGIN
DECLARE @pos1 INT,
@pos2 INT
SET @Order = REPLACE(
REPLACE(
@Order,
' asc',
' ASC'),
' desc',
' DESC')
IF CHARINDEX(
' DESC',
@Order)
> 0
IF CHARINDEX(
' ASC',
@Order)
> 0
BEGIN
IF CHARINDEX(
' DESC',
@Order)
< CHARINDEX(
' ASC',
@Order)
SET @Operator = '<='
ELSE
SET @Operator = '>='
END
ELSE
SET @Operator = '<='
ELSE
SET @Operator = '>='
SET @SortColumn = REPLACE(
REPLACE(
REPLACE(
@Order,
' ASC',
''),
' DESC',
''),
' ',
'')
SET @pos1 = CHARINDEX(
',',
@SortColumn)
IF @pos1 > 0
SET @SortColumn = SUBSTRING(
@SortColumn,
1,
@pos1-1)
SET @pos2 = CHARINDEX(
'.',
@SortColumn)
IF @pos2 > 0
BEGIN
SET @SortTable = SUBSTRING(
@SortColumn,
1,
@pos2-1)
IF @pos1 > 0
SET @SortName = SUBSTRING(
@SortColumn,
@pos2+1,
@pos1-@pos2-1)
ELSE
SET @SortName = SUBSTRING(
@SortColumn,
@pos2+1,
LEN(
@SortColumn)
-@pos2)
END
ELSE
BEGIN
SET @SortTable = @TableNames
SET @SortName = @SortColumn
END
END
ELSE
BEGIN
SET @SortColumn = @PrimaryKey
SET @SortTable = @TableNames
SET @SortName = @SortColumn
SET @Order = @SortColumn
SET @Operator = '>='
END
DECLARE @type varchar(
50)
DECLARE @prec int
SELECT @type=t.name,
@prec=c.prec
FROM sysobjects o
JOIN syscolumns c
on o.id
=c.id
JOIN systypes t
on c.xusertype
=t.xusertype
WHERE o.name
= @SortTable AND c.name
= @SortName
IF CHARINDEX(
'char',
@type)
> 0
SET @type = @type + '(' + CAST(
@prec AS varchar)
+ ')'
DECLARE @TopRows INT
SET @TopRows = @PageSize * @CurrentPage + 1
print @TopRows
print @Operator
EXEC(
'
DECLARE @SortColumnBegin ' + @type + '
SET ROWCOUNT ' + @TopRows + '
SELECT @SortColumnBegin=' + @SortColumn + ' FROM ' + @TableNames + ' ' + @Filter + ' ' + @Group + ' ORDER BY ' + @Order + '
SET ROWCOUNT ' + @PageSize + '
SELECT ' + @Fields + ' FROM ' + @TableNames + ' ' + @Filter + ' AND ' + @SortColumn + '' + @Operator + '@SortColumnBegin ' + @Group + ' ORDER BY ' + @Order + '
')
END
GO
--调用例子:
--1.单表/单排序
EXEC usp_PagingLarge
'bigtable',
'd_id',
'd_id,d_title,d_content,d_time',
20,
1,
'',
'',
'd_id desc'
--2.单表/多排序
EXEC usp_PagingLarge
'bigtable',
'd_id',
'*',
20,
0,
'',
'',
'd_time asc,d_id desc'
--3.多表/单排序
EXEC usp_PagingLarge
'bigtable left join bigtable_author on bigtable.d_id=bigtable_author.BigTable_id',
'bigtable.d_id',
'bigtable.d_id,bigtable.d_title,bigtable.d_content,bigtable.d_time,bigtable_author.d_author',
20,
0,
'',
'',
'bigtable.d_id asc'
--4.多表/多排序
EXEC usp_PagingLarge
'bigtable left join bigtable_author on bigtable.d_id=bigtable_author.BigTable_id',
'bigtable.d_id',
'bigtable.d_id,bigtable.d_title,bigtable.d_content,bigtable.d_time,bigtable_author.d_author',
20,
0,
'',
'',
'bigtable.d_time asc,bigtable.d_id desc'
与自定义分页结合例子:
/Files/jiny-z/Paging_Custom.rar
alter PROCEDURE SP_Pagination

/**//*
***************************************************************
** 分页存储过程 **
***************************************************************
参数说明:
1.Tables :表名称,视图
2.PrimaryKey :主关键字
3.Sort :排序语句,不带Order By 比如:NewsID Desc,OrderRows Asc
4.CurrentPage :当前页码
5.PageSize :分页尺寸
6.Fields:字段
6.Filter :过滤语句,不带Where
7.Group :Group语句,不带Group By
8.docount: 1返回总行数,0返回列表

***************************************************************/
(
@Tables varchar(
1000),
@PrimaryKey varchar(
100),
@Sort varchar(
200)
= NULL,
@CurrentPage int = 1,
@PageSize int = 10,
@Fields varchar(
1000)
= '*',
@Filter varchar(
1000)
= NULL,
@Group varchar(
1000)
= NULL,
@docount bit = 0
)
AS
/**//*默认排序*/
IF @Sort IS NULL OR @Sort = ''
SET @Sort = @PrimaryKey
DECLARE @SortTable varchar(
100)
DECLARE @SortName varchar(
100)
DECLARE @strSortColumn varchar(
200)
DECLARE @operator char(
2)
DECLARE @type varchar(
100)
DECLARE @prec int


/**//*设定排序语句.*/
IF CHARINDEX(
'DESC',
@Sort)
>0
BEGIN
SET @strSortColumn = REPLACE(
@Sort,
'DESC',
'')
SET @operator = '<='
END
ELSE
BEGIN
IF CHARINDEX(
'ASC',
@Sort)
= 0
SET @strSortColumn = REPLACE(
@Sort,
'ASC',
'')
SET @operator = '>='
END
IF CHARINDEX(
'.',
@strSortColumn)
> 0
BEGIN
SET @SortTable = SUBSTRING(
@strSortColumn,
0,
CHARINDEX(
'.',
@strSortColumn))
SET @SortName = SUBSTRING(
@strSortColumn,
CHARINDEX(
'.',
@strSortColumn)
+ 1,
LEN(
@strSortColumn))
END
ELSE
BEGIN
SET @SortTable = @Tables
SET @SortName = @strSortColumn
END
SELECT @type=t.name,
@prec=c.prec
FROM sysobjects o
JOIN syscolumns c
on o.id
=c.id
JOIN systypes t
on c.xusertype
=t.xusertype
WHERE o.name
= @SortTable AND c.name
= @SortName
IF CHARINDEX(
'char',
@type)
> 0
SET @type = @type + '(' + CAST(
@prec AS varchar)
+ ')'
DECLARE @strPageSize varchar(
50)
DECLARE @strStartRow varchar(
50)
DECLARE @strFilter varchar(
200)
DECLARE @strSimpleFilter varchar(
200)
DECLARE @strGroup varchar(
200)


/**//*默认当前页*/
IF @CurrentPage < 1
SET @CurrentPage = 1

/**//*设置分页参数.*/
SET @strPageSize = CAST(
@PageSize AS varchar(
50))
SET @strStartRow = CAST(((
@CurrentPage - 1)
*@PageSize + 1)
AS varchar(
50))


/**//*筛选以及分组语句.*/
IF @Filter IS NOT NULL AND @Filter != ''
BEGIN
SET @strFilter = ' WHERE ' + @Filter + ' '
SET @strSimpleFilter = ' AND ' + @Filter + ' '
END
ELSE
BEGIN
SET @strSimpleFilter = ''
SET @strFilter = ''
END
IF @Group IS NOT NULL AND @Group != ''
SET @strGroup = ' GROUP BY ' + @Group + ' '
ELSE
SET @strGroup = ''


declare @cTemp NVarChar(
1000)
declare @PageCount int,
@lineCount decimal
CREATE TABLE #
temp(linecount
INT)

set @cTemp = 'insert into #temp (linecount) select count(*) FROM ' + @Tables + @strFilter + ' ' + @strGroup 
exec (
@cTemp)

select @lineCount = linecount
from #
temp
drop table #
temp

if(
@docount=1)
begin
select @lineCount '总行数'
end
else 
begin
--得到总页数
set @PageCount = CEILING(
@lineCount/@strPageSize)
if @CurrentPage > @PageCount
begin
set @cTemp = 'SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE 1>2 '
end
else
begin

/**//*执行查询语句*/
set @cTemp = 'DECLARE @SortColumn ' + @type + '
SET ROWCOUNT ' + @strStartRow + '
SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
SET ROWCOUNT ' + @strPageSize + '
SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + ''
end
--print @cTemp
EXEC(
@cTemp)
end 
-----------------------------------------------------


分享]千万数量级分页存储过程(带效果演示)

效果演示:http:
//www.cn5135.com
/_App
/Opportunities
/QueryResult.aspx

CREATE PROCEDURE CN5135_SP_Pagination

/**//*
***************************************************************
** 千万数量级分页存储过程 **
***************************************************************
参数说明:
1.Tables :表名称,视图
2.PrimaryKey :主关键字
3.Sort :排序语句,不带Order By 比如:NewsID Desc,OrderRows Asc
4.CurrentPage :当前页码
5.PageSize :分页尺寸
6.Filter :过滤语句,不带Where
7.Group :Group语句,不带Group By
效果演示:http://www.cn5135.com/_App/Opportunities/QueryResult.aspx
***************************************************************/
(
@Tables varchar(
1000),
@PrimaryKey varchar(
100),
@Sort varchar(
200)
= NULL,
@CurrentPage int = 1,
@PageSize int = 10,
@Fields varchar(
1000)
= '*',
@Filter varchar(
1000)
= NULL,
@Group varchar(
1000)
= NULL
)
AS
/**//*默认排序*/
IF @Sort IS NULL OR @Sort = ''
SET @Sort = @PrimaryKey
DECLARE @SortTable varchar(
100)
DECLARE @SortName varchar(
100)
DECLARE @strSortColumn varchar(
200)
DECLARE @operator char(
2)
DECLARE @type varchar(
100)
DECLARE @prec int
/**//*设定排序语句.*/
IF CHARINDEX(
'DESC',
@Sort)
>0
BEGIN
SET @strSortColumn = REPLACE(
@Sort,
'DESC',
'')
SET @operator = '<='
END
ELSE
BEGIN
IF CHARINDEX(
'ASC',
@Sort)
= 0
SET @strSortColumn = REPLACE(
@Sort,
'ASC',
'')
SET @operator = '>='
END
IF CHARINDEX(
'.',
@strSortColumn)
> 0
BEGIN
SET @SortTable = SUBSTRING(
@strSortColumn,
0,
CHARINDEX(
'.',
@strSortColumn))
SET @SortName = SUBSTRING(
@strSortColumn,
CHARINDEX(
'.',
@strSortColumn)
+ 1,
LEN(
@strSortColumn))
END
ELSE
BEGIN
SET @SortTable = @Tables
SET @SortName = @strSortColumn
END
SELECT @type=t.name,
@prec=c.prec
FROM sysobjects o
JOIN syscolumns c
on o.id
=c.id
JOIN systypes t
on c.xusertype
=t.xusertype
WHERE o.name
= @SortTable AND c.name
= @SortName
IF CHARINDEX(
'char',
@type)
> 0
SET @type = @type + '(' + CAST(
@prec AS varchar)
+ ')'
DECLARE @strPageSize varchar(
50)
DECLARE @strStartRow varchar(
50)
DECLARE @strFilter varchar(
1000)
DECLARE @strSimpleFilter varchar(
1000)
DECLARE @strGroup varchar(
1000)

/**//*默认当前页*/
IF @CurrentPage < 1
SET @CurrentPage = 1
/**//*设置分页参数.*/
SET @strPageSize = CAST(
@PageSize AS varchar(
50))
SET @strStartRow = CAST(((
@CurrentPage - 1)
*@PageSize + 1)
AS varchar(
50))

/**//*筛选以及分组语句.*/
IF @Filter IS NOT NULL AND @Filter != ''
BEGIN
SET @strFilter = ' WHERE ' + @Filter + ' '
SET @strSimpleFilter = ' AND ' + @Filter + ' '
END
ELSE
BEGIN
SET @strSimpleFilter = ''
SET @strFilter = ''
END
IF @Group IS NOT NULL AND @Group != ''
SET @strGroup = ' GROUP BY ' + @Group + ' '
ELSE
SET @strGroup = ''
/**//*执行查询语句*/
EXEC(
'
DECLARE @SortColumn ' + @type + '
SET ROWCOUNT ' + @strStartRow + '
SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
SET ROWCOUNT ' + @strPageSize + '
SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
'
)
GO
注:
1)如表名参数为多表连接时,sort列必须指定表名;
2)只支持单字段排序,有朋友如果问为什么不做成可以多字段排序的,理论上确实有这种可能性,但需要以一定的效率损失为代价,而且会使方法过于复杂,如真有这种需要,完全可以写一个单独的分页存储过程,无论在性能还是复杂度上都比通用要简单.
3) 对非unique字段排序的支持,但必须设定一个PK字段(注:只要是unique字段都可以作为pk字段)
1
if exists (
select * from dbo.sysobjects
where id
= object_id(N
'[dbo].[Paging_RowCount]')
and OBJECTPROPERTY(id, N
'IsProcedure')
= 1)
2
drop procedure [dbo].
[Paging_RowCount] 3
GO 4
5
SET QUOTED_IDENTIFIER
ON 6
GO 7
SET ANSI_NULLS
ON 8
GO 9
---------------------------------------------------------------
10
-- 分页存储过程(使用RowCount) --edit by SiBen
11
-- summary:
12
-- 获取表或表集合的分页数据
13
-- 当多表连接时,sort列必须指定表名
14
---------------------------------------------------------------
15
16
CREATE PROCEDURE Paging_RowCount
17
(
18
@Tables varchar(
1000),
19
@PK varchar(
100),
20
@Sort varchar(
200)
= NULL,
21
@PageNumber int = 1,
22
@PageSize int = 10,
23
@Fields varchar(
1000)
= '*',
24
@Filter varchar(
1000)
= NULL,
25
@Group varchar(
1000)
= NULL,
26
@RecordCount int = 0 output
27
)
28
AS 29
30
/**//*Default Sorting*/ 31
IF @Sort IS NULL OR @Sort = '' 32
SET @Sort = @PK 33
34
/**//*Find the @PK type*/ 35
DECLARE @SortTable varchar(
100)
36
DECLARE @SortName varchar(
100)
37
DECLARE @strSortColumn varchar(
200)
38
DECLARE @operator char(
2)
39
DECLARE @type varchar(
100)
40
DECLARE @prec int 41
42
/**//*Set sorting variables.*/ 43
IF CHARINDEX(
'DESC',
@Sort)
>0 44
BEGIN 45
SET @strSortColumn = REPLACE(
@Sort,
'DESC',
'')
46
SET @operator = '<' 47
END 48
ELSE 49
BEGIN 50
IF CHARINDEX(
'ASC',
@Sort)
> 0 51
SET @strSortColumn = REPLACE(
@Sort,
'ASC',
'')
52
ELSE 53
SET @strSortColumn = @Sort 54
55
SET @operator = '>' 56
END 57
58
59
IF CHARINDEX(
'.',
@strSortColumn)
> 0 60
BEGIN 61
SET @SortTable = SUBSTRING(
@strSortColumn,
0,
CHARINDEX(
'.',
@strSortColumn))
62
SET @SortName = SUBSTRING(
@strSortColumn,
CHARINDEX(
'.',
@strSortColumn)
+ 1,
LEN(
@strSortColumn))
63
END 64
ELSE 65
BEGIN 66
SET @SortTable = @Tables 67
SET @SortName = @strSortColumn 68
END 69
70
SELECT @type=t.name,
@prec=c.prec
71
FROM sysobjects o
72
JOIN syscolumns c
on o.id
=c.id
73
JOIN systypes t
on c.xusertype
=t.xusertype
74
WHERE o.name
= @SortTable AND c.name
= @SortName 75
76
IF CHARINDEX(
'char',
@type)
> 0 77
SET @type = @type + '(' + CAST(
@prec AS varchar)
+ ')' 78
79
DECLARE @strPageSize varchar(
50)
80
DECLARE @strStartRow varchar(
50)
81
DECLARE @strFilter varchar(
1000)
82
DECLARE @strSimpleFilter varchar(
1000)
83
DECLARE @strGroup varchar(
1000)
84
85
/**//*Default Page Number*/ 86
IF @PageNumber < 1 87
SET @PageNumber = 1 88
89
/**//*Set paging variables.*/ 90
SET @strPageSize = CAST(
@PageSize AS varchar(
50))
91
SET @strStartRow = CAST(((
@PageNumber - 1)
*@PageSize + 1)
AS varchar(
50))
92
93
/**//*Set filter & group variables.*/ 94
IF @Filter IS NOT NULL AND @Filter != '' 95
BEGIN 96
SET @strFilter = ' WHERE ' + @Filter + ' ' 97
SET @strSimpleFilter = ' AND ' + @Filter + ' ' 98
END 99
ELSE100
BEGIN101
SET @strSimpleFilter = ''102
SET @strFilter = ''103
END104
IF @Group IS NOT NULL AND @Group != ''105
SET @strGroup = ' GROUP BY ' + @Group + ' '106
ELSE107
SET @strGroup = ''108
109
/**//*Get rows count.*/110
DECLARE @str_Count_SQL nvarchar(
500)
111
SET @str_Count_SQL= 'SELECT @TotalCount=count(*) FROM ' + @Tables + @strFilter112
EXEC sp_executesql
@str_Count_SQL,N
'@TotalCount int=0 output',
@RecordCount output
113
114
/**//*Execute dynamic query*/ 115
IF @Sort = @PK116
BEGIN117
EXEC(
118
'
119
DECLARE @SortColumn ' + @type + '
120
SET ROWCOUNT ' + @strStartRow + '
121
SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
122
SET ROWCOUNT ' + @strPageSize + '
123
SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + '= @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
124
'125
)
126
END127
ELSE128
BEGIN129
EXEC(
130
'
131
DECLARE @SortColumn ' + @type + '
132
DECLARE @SortNullValue ' + @type + '
133
DECLARE @PKStartValue int
134
SET @SortNullValue=CAST('''' as '+ @type +')
135
SET ROWCOUNT ' + @strStartRow + '
136
SELECT @SortColumn= isNull(' + @strSortColumn + ',@SortNullValue), @PKStartValue = '+ @PK +' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + ','+ @PK +' Desc
137
SET ROWCOUNT ' + @strPageSize + '
138
SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE (isNull(' + @strSortColumn+',@SortNullValue)' + @operator + ' @SortColumn or (isNull(' + @strSortColumn+',@SortNullValue)=@SortColumn and '+ @PK +'<=@PKStartValue))' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + ','+ @PK +' Desc
139
'140
)
141
END142
GO143
SET QUOTED_IDENTIFIER
OFF 144
GO145
SET ANSI_NULLS
ON 146
GO147
148
http://www.codeproject.com/aspnet/PagingLarge.asp

邹建的存储过程
ALTER PROC 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
本文转自高海东博客园博客,原文链接http://www.cnblogs.com/ghd258/archive/2006/01/11/314988.html,如需转载请自行联系原作者