CREATE
procedure
main_table_pwqzc
( @pagesize int ,
@pageindex int ,
@docount bit ,
@this_id )
as
if ( @docount = 1 )
begin
select count (id) from luntan where this_id = @this_id
end
else
begin
declare @indextable table (id int identity ( 1 , 1 ),nid int )
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound = ( @pageindex - 1 ) * @pagesize
set @PageUpperBound = @PageLowerBound + @pagesize
set rowcount @PageUpperBound
insert into @indextable (nid) select id from luntan where this_id = @this_id order by reply_time desc
select a. * from luntan a, @indextable t where a.id = t.nid
and t.id > @PageLowerBound and t.id <= @PageUpperBound order by t.id
end
GO
存储过程会根据传入的参数 @docount来确定是不是要返回所有要分页的记录总数
特别是这两行
set rowcount @PageUpperBound
insert into @indextable (nid) select id from luntan where this_id = @this_id order by reply_time desc
真的是妙不可言!! set rowcount @PageUpperBound当记录数达到@PageUpperBound时就会停止处理查询
, select id 只把id列取出放到临时表里, select a. * from luntan a, @indextable t where a.id = t.nid
and t.id > @PageLowerBound and t.id <= @PageUpperBound order by t.id 而这句也只从表中取出所需要的记录,而不是所有的记录,结合起来,极大的提高了效率!!
妙啊,真的妙!!!!
CREATE PROCEDURE Paging_RowCount
(
@Tables varchar ( 1000 ),
@PK varchar ( 100 ),
@Sort varchar ( 200 ) = NULL ,
@PageNumber int = 1 ,
@PageSize int = 10 ,
@Fields varchar ( 1000 ) = ' * ' ,
@Filter varchar ( 1000 ) = NULL ,
@Group varchar ( 1000 ) = NULL )
AS
/* Default Sorting */
IF @Sort IS NULL OR @Sort = ''
SET @Sort = @PK
/* Find the @PK type */
DECLARE @SortTable varchar ( 100 )
DECLARE @SortName varchar ( 100 )
DECLARE @strSortColumn varchar ( 200 )
DECLARE @operator char ( 2 )
DECLARE @type varchar ( 100 )
DECLARE @prec int
/* Set sorting variables. */
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 )
/* Default Page Number */
IF @PageNumber < 1
SET @PageNumber = 1
/* Set paging variables. */
SET @strPageSize = CAST ( @PageSize AS varchar ( 50 ))
SET @strStartRow = CAST ((( @PageNumber - 1 ) * @PageSize + 1 ) AS varchar ( 50 ))
/* Set filter & group variables. */
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 = ''
/* Execute dynamic query */
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
( @pagesize int ,
@pageindex int ,
@docount bit ,
@this_id )
as
if ( @docount = 1 )
begin
select count (id) from luntan where this_id = @this_id
end
else
begin
declare @indextable table (id int identity ( 1 , 1 ),nid int )
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound = ( @pageindex - 1 ) * @pagesize
set @PageUpperBound = @PageLowerBound + @pagesize
set rowcount @PageUpperBound
insert into @indextable (nid) select id from luntan where this_id = @this_id order by reply_time desc
select a. * from luntan a, @indextable t where a.id = t.nid
and t.id > @PageLowerBound and t.id <= @PageUpperBound order by t.id
end
GO
存储过程会根据传入的参数 @docount来确定是不是要返回所有要分页的记录总数
特别是这两行
set rowcount @PageUpperBound
insert into @indextable (nid) select id from luntan where this_id = @this_id order by reply_time desc
真的是妙不可言!! set rowcount @PageUpperBound当记录数达到@PageUpperBound时就会停止处理查询
, select id 只把id列取出放到临时表里, select a. * from luntan a, @indextable t where a.id = t.nid
and t.id > @PageLowerBound and t.id <= @PageUpperBound order by t.id 而这句也只从表中取出所需要的记录,而不是所有的记录,结合起来,极大的提高了效率!!
妙啊,真的妙!!!!
CREATE PROCEDURE Paging_RowCount
(
@Tables varchar ( 1000 ),
@PK varchar ( 100 ),
@Sort varchar ( 200 ) = NULL ,
@PageNumber int = 1 ,
@PageSize int = 10 ,
@Fields varchar ( 1000 ) = ' * ' ,
@Filter varchar ( 1000 ) = NULL ,
@Group varchar ( 1000 ) = NULL )
AS
/* Default Sorting */
IF @Sort IS NULL OR @Sort = ''
SET @Sort = @PK
/* Find the @PK type */
DECLARE @SortTable varchar ( 100 )
DECLARE @SortName varchar ( 100 )
DECLARE @strSortColumn varchar ( 200 )
DECLARE @operator char ( 2 )
DECLARE @type varchar ( 100 )
DECLARE @prec int
/* Set sorting variables. */
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 )
/* Default Page Number */
IF @PageNumber < 1
SET @PageNumber = 1
/* Set paging variables. */
SET @strPageSize = CAST ( @PageSize AS varchar ( 50 ))
SET @strStartRow = CAST ((( @PageNumber - 1 ) * @PageSize + 1 ) AS varchar ( 50 ))
/* Set filter & group variables. */
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 = ''
/* Execute dynamic query */
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