分页存储过程-阿里云开发者社区

开发者社区> 老朱教授> 正文

分页存储过程

简介:
+关注继续查看

ExpandedBlockStart.gifContractedBlock.gif/**//**//**//*
InBlock.gif说明:1.支持多表查询 2.支持任意排序 3.不支持表别名
InBlock.gif参考了
InBlock.gif evafly920:[分享]千万数量级分页存储过程(效果演示)
InBlock.gif 地址:http://blog.csdn.net/evafly920/archive/2006/03/03/614813.aspx
InBlock.gif
InBlock.gifIF(EXISTS(SELECT * FROM sysobjects WHERE [id]=OBJECT_ID('usp_PagingLarge') AND xtype='P'))
InBlock.gif DROP PROCEDURE usp_PagingLarge
ExpandedBlockEnd.gif
*/

None.gif
None.gifGO
None.gif
None.gifCREATE PROCEDURE usp_PagingLarge
None.gif@TableNames VARCHAR(200), --表名,可以是多个表,但不能用别名
None.gif
@PrimaryKey VARCHAR(100), --主键,可以为空,但@Order为空时该值不能为空
None.gif
@Fields VARCHAR(200), --要取出的字段,可以是多个表的字段,可以为空,为空表示select *
None.gif
@PageSize INT, --每页记录数
None.gif
@CurrentPage INT, --当前页,0表示第1页
None.gif
@Filter VARCHAR(200) = '', --条件,可以为空,不用填 where
None.gif
@Group VARCHAR(200) = '', --分组依据,可以为空,不用填 group by
None.gif
@Order VARCHAR(200) = '' --排序,可以为空,为空默认按主键升序排列,不用填 order by
None.gif
AS
None.gifBEGIN
None.gifDECLARE @SortColumn VARCHAR(200)
None.gifDECLARE @Operator CHAR(2)
None.gifDECLARE @SortTable VARCHAR(200)
None.gifDECLARE @SortName VARCHAR(200)
None.gifIF @Fields = ''
None.gifSET @Fields = '*'
None.gifIF @Filter = ''
None.gifSET @Filter = 'WHERE 1=1'
None.gifELSE
None.gifSET @Filter = 'WHERE ' + @Filter
None.gifIF @Group <>''
None.gifSET @Group = 'GROUP BY ' + @Group
None.gif
None.gifIF @Order <> ''
None.gifBEGIN
None.gifDECLARE @pos1 INT, @pos2 INT
None.gifSET @Order = REPLACE(REPLACE(@Order, ' asc', ' ASC'), ' desc', ' DESC')
None.gifIF CHARINDEX(' DESC', @Order) > 0
None.gifIF CHARINDEX(' ASC', @Order) > 0
None.gifBEGIN
None.gifIF CHARINDEX(' DESC', @Order) < CHARINDEX(' ASC', @Order)
None.gifSET @Operator = '<='
None.gifELSE
None.gifSET @Operator = '>='
None.gifEND
None.gifELSE
None.gifSET @Operator = '<='
None.gifELSE
None.gifSET @Operator = '>='
None.gifSET @SortColumn = REPLACE(REPLACE(REPLACE(@Order, ' ASC', ''), ' DESC', ''), ' ', '')
None.gifSET @pos1 = CHARINDEX(',', @SortColumn)
None.gifIF @pos1 > 0
None.gifSET @SortColumn = SUBSTRING(@SortColumn, 1, @pos1-1)
None.gifSET @pos2 = CHARINDEX('.', @SortColumn)
None.gifIF @pos2 > 0
None.gifBEGIN
None.gifSET @SortTable = SUBSTRING(@SortColumn, 1, @pos2-1)
None.gifIF @pos1 > 0
None.gifSET @SortName = SUBSTRING(@SortColumn, @pos2+1, @pos1-@pos2-1)
None.gifELSE
None.gifSET @SortName = SUBSTRING(@SortColumn, @pos2+1, LEN(@SortColumn)-@pos2)
None.gifEND
None.gifELSE
None.gifBEGIN
None.gifSET @SortTable = @TableNames
None.gifSET @SortName = @SortColumn
None.gifEND
None.gifEND
None.gifELSE
None.gifBEGIN
None.gifSET @SortColumn = @PrimaryKey
None.gifSET @SortTable = @TableNames
None.gifSET @SortName = @SortColumn
None.gifSET @Order = @SortColumn
None.gifSET @Operator = '>='
None.gifEND
None.gif
None.gifDECLARE @type varchar(50)
None.gifDECLARE @prec int
None.gifSELECT @type=t.name, @prec=c.prec
None.gifFROM sysobjects o
None.gifJOIN syscolumns c on o.id=c.id
None.gifJOIN systypes t on c.xusertype=t.xusertype
None.gifWHERE o.name = @SortTable AND c.name = @SortName
None.gifIF CHARINDEX('char', @type) > 0
None.gifSET @type = @type + '(' + CAST(@prec AS varchar) + ')'
None.gif
None.gifDECLARE @TopRows INT
None.gifSET @TopRows = @PageSize * @CurrentPage + 1
None.gifprint @TopRows
None.gifprint @Operator
None.gifEXEC('
None.gif DECLARE @SortColumnBegin
' + @type + '
None.gif SET ROWCOUNT
' + @TopRows + '
None.gif SELECT @SortColumnBegin=
' + @SortColumn + ' FROM ' + @TableNames + ' ' + @Filter + ' ' + @Group + ' ORDER BY ' + @Order + '
None.gif SET ROWCOUNT
' + @PageSize + '
None.gif SELECT
' + @Fields + ' FROM ' + @TableNames + ' ' + @Filter + ' AND ' + @SortColumn + '' + @Operator + '@SortColumnBegin ' + @Group + ' ORDER BY ' + @Order + '
None.gif
')
None.gifEND
None.gif
None.gifGO
None.gif
None.gif--调用例子:
None.gif
--1.单表/单排序
None.gif
EXEC usp_PagingLarge 'bigtable','d_id','d_id,d_title,d_content,d_time',20,1,'','','d_id desc'
None.gif--2.单表/多排序
None.gif
EXEC usp_PagingLarge 'bigtable','d_id','*',20,0,'','','d_time asc,d_id desc'
None.gif--3.多表/单排序
None.gif
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'
None.gif--4.多表/多排序
None.gif
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
None.gifalter PROCEDURE SP_Pagination
ExpandedBlockStart.gifContractedBlock.gif/**//*
InBlock.gif***************************************************************
InBlock.gif** 分页存储过程 **
InBlock.gif***************************************************************
InBlock.gif参数说明:
InBlock.gif1.Tables :表名称,视图
InBlock.gif2.PrimaryKey :主关键字
InBlock.gif3.Sort :排序语句,不带Order By 比如:NewsID Desc,OrderRows Asc
InBlock.gif4.CurrentPage :当前页码
InBlock.gif5.PageSize :分页尺寸
InBlock.gif6.Fields:字段
InBlock.gif6.Filter :过滤语句,不带Where
InBlock.gif7.Group :Group语句,不带Group By
InBlock.gif8.docount: 1返回总行数,0返回列表
InBlock.gif
ExpandedBlockEnd.gif**************************************************************
*/

None.gif(
None.gif@Tables varchar(1000),
None.gif@PrimaryKey varchar(100),
None.gif@Sort varchar(200) = NULL,
None.gif@CurrentPage int = 1,
None.gif@PageSize int = 10,
None.gif@Fields varchar(1000) = '*',
None.gif@Filter varchar(1000) = NULL,
None.gif@Group varchar(1000) = NULL,
None.gif@docount bit = 0
None.gif)
None.gifAS
ExpandedBlockStart.gifContractedBlock.gif/**//*默认排序*/
None.gifIF @Sort IS NULL OR @Sort = ''
None.gifSET @Sort = @PrimaryKey
None.gifDECLARE @SortTable varchar(100)
None.gifDECLARE @SortName varchar(100)
None.gifDECLARE @strSortColumn varchar(200)
None.gifDECLARE @operator char(2)
None.gifDECLARE @type varchar(100)
None.gifDECLARE @prec int
None.gif
None.gif
ExpandedBlockStart.gifContractedBlock.gif/**//*设定排序语句.*/
None.gifIF CHARINDEX('DESC',@Sort)>0
None.gifBEGIN
None.gifSET @strSortColumn = REPLACE(@Sort, 'DESC', '')
None.gifSET @operator = '<='
None.gifEND
None.gifELSE
None.gifBEGIN
None.gifIF CHARINDEX('ASC', @Sort) = 0
None.gifSET @strSortColumn = REPLACE(@Sort, 'ASC', '')
None.gifSET @operator = '>='
None.gifEND
None.gifIF CHARINDEX('.', @strSortColumn) > 0
None.gifBEGIN
None.gifSET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))
None.gifSET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn))
None.gifEND
None.gifELSE
None.gifBEGIN
None.gifSET @SortTable = @Tables
None.gifSET @SortName = @strSortColumn
None.gifEND
None.gif
None.gifSELECT @type=t.name, @prec=c.prec
None.gifFROM sysobjects o
None.gifJOIN syscolumns c on o.id=c.id
None.gifJOIN systypes t on c.xusertype=t.xusertype
None.gifWHERE o.name = @SortTable AND c.name = @SortName
None.gif
None.gifIF CHARINDEX('char', @type) > 0
None.gifSET @type = @type + '(' + CAST(@prec AS varchar) + ')'
None.gif
None.gifDECLARE @strPageSize varchar(50)
None.gifDECLARE @strStartRow varchar(50)
None.gifDECLARE @strFilter varchar(200)
None.gifDECLARE @strSimpleFilter varchar(200)
None.gifDECLARE @strGroup varchar(200)
None.gif
ExpandedBlockStart.gifContractedBlock.gif/**//*默认当前页*/
None.gifIF @CurrentPage < 1
None.gifSET @CurrentPage = 1
None.gif
ExpandedBlockStart.gifContractedBlock.gif/**//*设置分页参数.*/
None.gifSET @strPageSize = CAST(@PageSize AS varchar(50))
None.gifSET @strStartRow = CAST(((@CurrentPage - 1)*@PageSize + 1) AS varchar(50))
None.gif
ExpandedBlockStart.gifContractedBlock.gif/**//*筛选以及分组语句.*/
None.gifIF @Filter IS NOT NULL AND @Filter != ''
None.gifBEGIN
None.gifSET @strFilter = ' WHERE ' + @Filter + ' '
None.gifSET @strSimpleFilter = ' AND ' + @Filter + ' '
None.gifEND
None.gifELSE
None.gifBEGIN
None.gifSET @strSimpleFilter = ''
None.gifSET @strFilter = ''
None.gifEND
None.gif
None.gifIF @Group IS NOT NULL AND @Group != ''
None.gifSET @strGroup = ' GROUP BY ' + @Group + ' '
None.gifELSE
None.gifSET @strGroup = ''
None.gif
None.gif
None.gif
None.gifdeclare @cTemp NVarChar(1000)
None.gifdeclare @PageCount int, @lineCount decimal
None.gif
None.gifCREATE TABLE #temp(linecount INT)
None.gif
None.gifset @cTemp = 'insert into #temp (linecount) select count(*) FROM ' + @Tables + @strFilter + ' ' + @strGroup
None.gif
None.gifexec (@cTemp)
None.gif
None.gifselect @lineCount = linecount from #temp
None.gif
None.gifdrop table #temp
None.gif
None.gif
None.gifif(@docount=1)
None.gifbegin
None.gifselect @lineCount '总行数'
None.gifend
None.gifelse
None.gif
None.gifbegin
None.gif
None.gif--得到总页数
None.gif
set @PageCount = CEILING(@lineCount/@strPageSize)
None.gifif @CurrentPage > @PageCount
None.gifbegin
None.gifset @cTemp = 'SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE 1>2 '
None.gifend
None.gifelse
None.gifbegin
None.gif
ExpandedBlockStart.gifContractedBlock.gif/**//*执行查询语句*/
None.gif
None.gifset @cTemp = 'DECLARE @SortColumn ' + @type + '
None.gif SET ROWCOUNT
' + @strStartRow + '
None.gif SELECT @SortColumn=
' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
None.gif SET ROWCOUNT
' + @strPageSize + '
None.gif SELECT
' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + ''
None.gif
None.gifend
None.gif
None.gif--print @cTemp
None.gif
EXEC(@cTemp)
None.gifend
None.gif
None.gif-----------------------------------------------------
None.gif

None.gif

None.gif分页查询的方法已经很多很多,在这里我也加入成为其中一员。
None.gif SQL Server中有一个Set Rowcount的的设置,它的意思是使命令的处理在响应指定的行数之后停止处理命令,利用这个特点,我们可以借用它来在一个千万行级数据表中实现高性能分页查询。先来说说实现方式:
None.gif1、我们来假定Table中有一个已经建立了索引的主键字段ID(整数型),我们将按照这个字段来取数据进行分页。
None.gif2、页的大小我们放在@PageSize中
None.gif3、当前页号我们放在@CurrentPage中
None.gif4、如何让记录指针快速滚动到我们要取的数据开头的那一行呢,这是关键所在!有了Set RowCount,我们就很容易实现了。
None.gif5、如果我们成功地滚动记录指针到我们要取的数据的开头的那一行,然后我们把那一行的记录的ID字段的值记录下来,那么,利用Top和条件,我们就很容易的得到指定页的数据了。当然,有了Set RowCount,我们难道还用Top么?
None.gif 看看Set Rowcount怎么来帮我们的忙吧:
None.gif
None.gifDeclare @ID int
None.gifDeclare @MoveRecords int
None.gif
None.gif--@CurrentPage和@PageSize是传入参数
None.gif
Set @MoveRecords=@CurrentPage * @PageSize+1
None.gif
None.gif--下面两行实现快速滚动到我们要取的数据的行,并把ID记录下来
None.gif
Set Rowcount @MoveRecords
None.gifSelect @ID=ID from Table1 Order by ID
None.gif
None.gifSet Rowcount @PageSize
None.gif--最恨为了减少麻烦使用*了,但是在这里为了说明方便,暂时用一下
None.gif
Select * From Table1 Where ID>=@ID Order By ID
None.gifSet Rowcount 0
None.gif 大家可以试试看,在一个1千W记录的表里面,一下子方翻页到第100页(每页100条),看看有多快!
None.gif

None.gif分享]千万数量级分页存储过程(带效果演示)
None.gif效果演示:http://www.cn5135.com/_App/Opportunities/QueryResult.aspx
None.gif
None.gifCREATE PROCEDURE CN5135_SP_Pagination
ExpandedBlockStart.gifContractedBlock.gif/**//*
InBlock.gif***************************************************************
InBlock.gif** 千万数量级分页存储过程 **
InBlock.gif***************************************************************
InBlock.gif参数说明:
InBlock.gif1.Tables :表名称,视图
InBlock.gif2.PrimaryKey :主关键字
InBlock.gif3.Sort :排序语句,不带Order By 比如:NewsID Desc,OrderRows Asc
InBlock.gif4.CurrentPage :当前页码
InBlock.gif5.PageSize :分页尺寸
InBlock.gif6.Filter :过滤语句,不带Where
InBlock.gif7.Group :Group语句,不带Group By
InBlock.gif效果演示:http://www.cn5135.com/_App/Opportunities/QueryResult.aspx
ExpandedBlockEnd.gif**************************************************************
*/

None.gif(
None.gif@Tables varchar(1000),
None.gif@PrimaryKey varchar(100),
None.gif@Sort varchar(200) = NULL,
None.gif@CurrentPage int = 1,
None.gif@PageSize int = 10,
None.gif@Fields varchar(1000) = '*',
None.gif@Filter varchar(1000) = NULL,
None.gif@Group varchar(1000) = NULL
None.gif)
None.gifAS
ExpandedBlockStart.gifContractedBlock.gif/**//*默认排序*/
None.gifIF @Sort IS NULL OR @Sort = ''
None.gifSET @Sort = @PrimaryKey
None.gifDECLARE @SortTable varchar(100)
None.gifDECLARE @SortName varchar(100)
None.gifDECLARE @strSortColumn varchar(200)
None.gifDECLARE @operator char(2)
None.gifDECLARE @type varchar(100)
None.gifDECLARE @prec int
ExpandedBlockStart.gifContractedBlock.gif/**//*设定排序语句.*/
None.gifIF CHARINDEX('DESC',@Sort)>0
None.gifBEGIN
None.gifSET @strSortColumn = REPLACE(@Sort, 'DESC', '')
None.gifSET @operator = '<='
None.gifEND
None.gifELSE
None.gifBEGIN
None.gifIF CHARINDEX('ASC', @Sort) = 0
None.gifSET @strSortColumn = REPLACE(@Sort, 'ASC', '')
None.gifSET @operator = '>='
None.gifEND
None.gifIF CHARINDEX('.', @strSortColumn) > 0
None.gifBEGIN
None.gifSET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))
None.gifSET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn))
None.gifEND
None.gifELSE
None.gifBEGIN
None.gifSET @SortTable = @Tables
None.gifSET @SortName = @strSortColumn
None.gifEND
None.gifSELECT @type=t.name, @prec=c.prec
None.gifFROM sysobjects o
None.gifJOIN syscolumns c on o.id=c.id
None.gifJOIN systypes t on c.xusertype=t.xusertype
None.gifWHERE o.name = @SortTable AND c.name = @SortName
None.gifIF CHARINDEX('char', @type) > 0
None.gifSET @type = @type + '(' + CAST(@prec AS varchar) + ')'
None.gifDECLARE @strPageSize varchar(50)
None.gifDECLARE @strStartRow varchar(50)
None.gifDECLARE @strFilter varchar(1000)
None.gifDECLARE @strSimpleFilter varchar(1000)
None.gifDECLARE @strGroup varchar(1000)
ExpandedBlockStart.gifContractedBlock.gif/**//*默认当前页*/
None.gifIF @CurrentPage < 1
None.gifSET @CurrentPage = 1
ExpandedBlockStart.gifContractedBlock.gif/**//*设置分页参数.*/
None.gifSET @strPageSize = CAST(@PageSize AS varchar(50))
None.gifSET @strStartRow = CAST(((@CurrentPage - 1)*@PageSize + 1) AS varchar(50))
ExpandedBlockStart.gifContractedBlock.gif/**//*筛选以及分组语句.*/
None.gifIF @Filter IS NOT NULL AND @Filter != ''
None.gifBEGIN
None.gifSET @strFilter = ' WHERE ' + @Filter + ' '
None.gifSET @strSimpleFilter = ' AND ' + @Filter + ' '
None.gifEND
None.gifELSE
None.gifBEGIN
None.gifSET @strSimpleFilter = ''
None.gifSET @strFilter = ''
None.gifEND
None.gifIF @Group IS NOT NULL AND @Group != ''
None.gifSET @strGroup = ' GROUP BY ' + @Group + ' '
None.gifELSE
None.gifSET @strGroup = ''
ExpandedBlockStart.gifContractedBlock.gif/**//*执行查询语句*/
None.gifEXEC(
None.gif'
None.gifDECLARE @SortColumn
' + @type + '
None.gifSET ROWCOUNT
' + @strStartRow + '
None.gifSELECT @SortColumn=
' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
None.gifSET ROWCOUNT
' + @strPageSize + '
None.gifSELECT
' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
None.gif
'
None.gif)
None.gifGO

注:
1)如表名参数为多表连接时,sort列必须指定表名;
2)只支持单字段排序,有朋友如果问为什么不做成可以多字段排序的,理论上确实有这种可能性,但需要以一定的效率损失为代价,而且会使方法过于复杂,如真有这种需要,完全可以写一个单独的分页存储过程,无论在性能还是复杂度上都比通用要简单.
3) 对非unique字段排序的支持,但必须设定一个PK字段(注:只要是unique字段都可以作为pk字段)


1None.gifif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Paging_RowCount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
2None.gifdrop procedure [dbo].[Paging_RowCount]
3None.gifGO
4None.gif
5None.gifSET QUOTED_IDENTIFIER ON
6None.gifGO
7None.gifSET ANSI_NULLS ON
8None.gifGO
9None.gif---------------------------------------------------------------
10None.gif-- 分页存储过程(使用RowCount) --edit by SiBen
11None.gif-- summary:
12None.gif-- 获取表或表集合的分页数据
13None.gif-- 当多表连接时,sort列必须指定表名
14None.gif---------------------------------------------------------------
15None.gif
16None.gifCREATE PROCEDURE Paging_RowCount
17None.gif(
18None.gif@Tables varchar(1000),
19None.gif@PK varchar(100),
20None.gif@Sort varchar(200) = NULL,
21None.gif@PageNumber int = 1,
22None.gif@PageSize int = 10,
23None.gif@Fields varchar(1000) = '*',
24None.gif@Filter varchar(1000) = NULL,
25None.gif@Group varchar(1000) = NULL,
26None.gif@RecordCount int = 0 output
27None.gif)
28None.gifAS
29None.gif
30ExpandedBlockStart.gifContractedBlock.gif/**//*Default Sorting*/
31None.gifIF @Sort IS NULL OR @Sort = ''
32None.gifSET @Sort = @PK
33None.gif
34ExpandedBlockStart.gifContractedBlock.gif/**//*Find the @PK type*/
35None.gifDECLARE @SortTable varchar(100)
36None.gifDECLARE @SortName varchar(100)
37None.gifDECLARE @strSortColumn varchar(200)
38None.gifDECLARE @operator char(2)
39None.gifDECLARE @type varchar(100)
40None.gifDECLARE @prec int
41None.gif
42ExpandedBlockStart.gifContractedBlock.gif/**//*Set sorting variables.*/
43None.gifIF CHARINDEX('DESC',@Sort)>0
44None.gifBEGIN
45None.gifSET @strSortColumn = REPLACE(@Sort, 'DESC', '')
46None.gifSET @operator = '<'
47None.gifEND
48None.gifELSE
49None.gifBEGIN
50None.gifIF CHARINDEX('ASC', @Sort) > 0
51None.gifSET @strSortColumn = REPLACE(@Sort, 'ASC', '')
52None.gifELSE
53None.gifSET @strSortColumn = @Sort
54None.gif
55None.gifSET @operator = '>'
56None.gifEND
57None.gif
58None.gif
59None.gifIF CHARINDEX('.', @strSortColumn) > 0
60None.gifBEGIN
61None.gifSET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))
62None.gifSET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn))
63None.gifEND
64None.gifELSE
65None.gifBEGIN
66None.gifSET @SortTable = @Tables
67None.gifSET @SortName = @strSortColumn
68None.gifEND
69None.gif
70None.gifSELECT @type=t.name, @prec=c.prec
71None.gifFROM sysobjects o
72None.gifJOIN syscolumns c on o.id=c.id
73None.gifJOIN systypes t on c.xusertype=t.xusertype
74None.gifWHERE o.name = @SortTable AND c.name = @SortName
75None.gif
76None.gifIF CHARINDEX('char', @type) > 0
77None.gifSET @type = @type + '(' + CAST(@prec AS varchar) + ')'
78None.gif
79None.gifDECLARE @strPageSize varchar(50)
80None.gifDECLARE @strStartRow varchar(50)
81None.gifDECLARE @strFilter varchar(1000)
82None.gifDECLARE @strSimpleFilter varchar(1000)
83None.gifDECLARE @strGroup varchar(1000)
84None.gif
85ExpandedBlockStart.gifContractedBlock.gif/**//*Default Page Number*/
86None.gifIF @PageNumber < 1
87None.gifSET @PageNumber = 1
88None.gif
89ExpandedBlockStart.gifContractedBlock.gif/**//*Set paging variables.*/
90None.gifSET @strPageSize = CAST(@PageSize AS varchar(50))
91None.gifSET @strStartRow = CAST(((@PageNumber - 1)*@PageSize + 1) AS varchar(50))
92None.gif
93ExpandedBlockStart.gifContractedBlock.gif/**//*Set filter & group variables.*/
94None.gifIF @Filter IS NOT NULL AND @Filter != ''
95None.gifBEGIN
96None.gifSET @strFilter = ' WHERE ' + @Filter + ' '
97None.gifSET @strSimpleFilter = ' AND ' + @Filter + ' '
98None.gifEND
99None.gifELSE
100None.gifBEGIN
101None.gifSET @strSimpleFilter = ''
102None.gifSET @strFilter = ''
103None.gifEND
104None.gifIF @Group IS NOT NULL AND @Group != ''
105None.gifSET @strGroup = ' GROUP BY ' + @Group + ' '
106None.gifELSE
107None.gifSET @strGroup = ''
108None.gif
109ExpandedBlockStart.gifContractedBlock.gif/**//*Get rows count.*/
110None.gifDECLARE @str_Count_SQL nvarchar(500)
111None.gifSET @str_Count_SQL= 'SELECT @TotalCount=count(*) FROM ' + @Tables + @strFilter
112None.gifEXEC sp_executesql @str_Count_SQL,N'@TotalCount int=0 output',@RecordCount output
113None.gif
114ExpandedBlockStart.gifContractedBlock.gif/**//*Execute dynamic query*/
115None.gifIF @Sort = @PK
116None.gifBEGIN
117None.gifEXEC(
118None.gif'
119None.gif DECLARE @SortColumn ' + @type + '
120None.gif SET ROWCOUNT ' + @strStartRow + '
121None.gif SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
122None.gif SET ROWCOUNT ' + @strPageSize + '
123None.gif SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + '= @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
124None.gif'
125None.gif )
126None.gifEND
127None.gifELSE
128None.gifBEGIN
129None.gifEXEC(
130None.gif'
131None.gif DECLARE @SortColumn ' + @type + '
132None.gif DECLARE @SortNullValue ' + @type + '
133None.gif DECLARE @PKStartValue int
134None.gif SET @SortNullValue=CAST('''' as '+ @type +')
135None.gif SET ROWCOUNT ' + @strStartRow + '
136None.gif SELECT @SortColumn= isNull(' + @strSortColumn + ',@SortNullValue), @PKStartValue = '+ @PK +' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + ','+ @PK +' Desc
137None.gif SET ROWCOUNT ' + @strPageSize + '
138None.gif 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
139None.gif'
140None.gif )
141None.gifEND
142None.gifGO
143None.gifSET QUOTED_IDENTIFIER OFF
144None.gifGO
145None.gifSET ANSI_NULLS ON
146None.gifGO
147None.gif
148None.gif
http://www.codeproject.com/aspnet/PagingLarge.asp
None.gif邹建的存储过程
None.gifALTER PROC PageView
None.gif@tbname sysname, --要分页显示的表名
None.gif
@FieldKey sysname, --用于定位记录的主键(惟一键)字段,只能是单个字段
None.gif
@PageCurrent int=1, --要显示的页码
None.gif
@PageSize int=10, --每页的大小(记录数)
None.gif
@FieldShow nvarchar(1000)='', --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
None.gif
@FieldOrder nvarchar(1000)='', --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC
None.gif
--用于指定排序顺序
None.gif
@Where nvarchar(1000)='', --查询条件
None.gif
@PageCount int OUTPUT --总页数
None.gif
AS
None.gifDECLARE @sql nvarchar(4000)
None.gifSET NOCOUNT ON
None.gif--检查对象是否有效
None.gif
IF OBJECT_ID(@tbname) IS NULL
None.gifBEGIN
None.gifRAISERROR(N'对象"%s"不存在',1,16,@tbname)
None.gifRETURN
None.gifEND
None.gifIF OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTable')=0
None.gifAND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsView')=0
None.gifAND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTableFunction')=0
None.gifBEGIN
None.gifRAISERROR(N'"%s"不是表、视图或者表值函数',1,16,@tbname)
None.gifRETURN
None.gifEND
None.gif
None.gif--分页字段检查
None.gif
IF ISNULL(@FieldKey,N'')=''
None.gifBEGIN
None.gifRAISERROR(N'分页处理需要主键(或者惟一键)',1,16)
None.gifRETURN
None.gifEND
None.gif
None.gif--其他参数检查及规范
None.gif
IF ISNULL(@PageCurrent,0)<1 SET @PageCurrent=1
None.gifIF ISNULL(@PageSize,0)<1 SET @PageSize=10
None.gifIF ISNULL(@FieldShow,N'')=N'' SET @FieldShow=N'*'
None.gifIF ISNULL(@FieldOrder,N'')=N''
None.gifSET @FieldOrder=N''
None.gifELSE
None.gifSET @FieldOrder=N'ORDER BY '+LTRIM(@FieldOrder)
None.gifIF ISNULL(@Where,N'')=N''
None.gifSET @Where=N''
None.gifELSE
None.gifSET @Where=N'WHERE ('+@Where+N')'
None.gif
None.gif--如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值)
None.gif
IF @PageCount IS NULL
None.gifBEGIN
None.gifSET @sql=N'SELECT @PageCount=COUNT(*)'
None.gif+N' FROM '+@tbname
None.gif+N' '+@Where
None.gifEXEC sp_executesql @sql,N'@PageCount int OUTPUT',@PageCount OUTPUT
None.gifSET @PageCount=(@PageCount+@PageSize-1)/@PageSize
None.gifEND
None.gif
None.gif--计算分页显示的TOPN值
None.gif
DECLARE @TopN varchar(20),@TopN1 varchar(20)
None.gifSELECT @TopN=@PageSize,
None.gif@TopN1=@PageCurrent*@PageSize
None.gif
None.gif--第一页直接显示
None.gif
IF @PageCurrent=1
None.gifEXEC(N'SELECT TOP '+@TopN
None.gif+N' '+@FieldShow
None.gif+N' FROM '+@tbname
None.gif+N' '+@Where
None.gif+N' '+@FieldOrder)
None.gifELSE
None.gifBEGIN
None.gifSELECT @PageCurrent=@TopN1,
None.gif@sql=N'SELECT @n=@n-1,@s=CASE WHEN @n<'+@TopN
None.gif+N' THEN @s+N'',''+QUOTENAME(RTRIM(CAST('+@FieldKey
None.gif+N' as varchar(8000))),N'''''''') ELSE N'''' END FROM '+@tbname
None.gif+N' '+@Where
None.gif+N' '+@FieldOrder
None.gifSET ROWCOUNT @PageCurrent
None.gifEXEC sp_executesql @sql,
None.gifN'@n int,@s nvarchar(4000) OUTPUT',
None.gif@PageCurrent,@sql OUTPUT
None.gifSET ROWCOUNT 0
None.gifIF @sql=N''
None.gifEXEC(N'SELECT TOP 0'
None.gif+N' '+@FieldShow
None.gif+N' FROM '+@tbname)
None.gifELSE
None.gifBEGIN
None.gifSET @sql=STUFF(@sql,1,1,N'')
None.gif--执行查询
None.gif
EXEC(N'SELECT TOP '+@TopN
None.gif+N' '+@FieldShow
None.gif+N' FROM '+@tbname
None.gif+N' WHERE '+@FieldKey
None.gif+N' IN('+@sql
None.gif+N') '+@FieldOrder)
None.gifEND
None.gifEND



本文转自高海东博客园博客,原文链接http://www.cnblogs.com/ghd258/archive/2006/01/11/314988.html,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
sqlserver 通用分页存储过程
来源:http://www.jb51.net/article/19936.htm CREATE PROCEDURE commonPagination @columns varchar(500), --要显示的列名,用逗号隔开 @tableName varchar(100), --要查询...
672 0
阿里云服务器怎么设置密码?怎么停机?怎么重启服务器?
如果在创建实例时没有设置密码,或者密码丢失,您可以在控制台上重新设置实例的登录密码。本文仅描述如何在 ECS 管理控制台上修改实例登录密码。
7987 0
Mysql分页查询通用存储过程 - 阿影的黄金时代 - 博客频道 - CSDN.NET
来源:http://blog.csdn.net/fcrpg2005/article/details/1522713#1536434-hi-1-63006-42d97150898b1af15ddaae52f91f09c2
664 0
通过存储过程进行分页查询的SQL示例
--创建人:zengfanlong --创建时间:2014-7-28 10:51:15 --说明:根据公司简写代码获取当前待同步的气瓶档案数据(分页获取) ALTER PROCEDURE [UP_GasBottles_GetSyncData_ByPage] ( @C...
620 0
mysql存储过程
此存储过程实例,主要注意 在存储过程中 if 语句的使用,已经如果存在则不添加 not exists的使用, insert into table_name(column1) select column1 from table_name2 where colunm2=’condition1’ and not exists (select 1 from table_na
960 0
通用分页存储过程
/*--实现分页的通用存储过程 显示指定表、视图、查询结果的第X页对于表中主键或标识列的情况,直接从原表取数查询,其它情况使用临时表的方法如果视图或查询结果中有主键,不推荐此方法如果使用查询语...
522 0
jdbc_分页查询,大数据,批处理,存储过程
分页查询 实际上就是每次查询一部分记录,并显示: select * from table_name limit StartIndex, PageSize;——>StartIndex:从第几个记录开始查。
696 0
C# 调用存储过程操作 OUTPUT参数和Return返回值
本文转载:http://www.cnblogs.com/libingql/archive/2010/05/02/1726104.html   存储过程是存放在数据库服务器上的预先编译好的sql语句。使用存储过程,可以直接在数据库中存储并运行功能强大的任务。
862 0
+关注
3546
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载