存儲(chǔ)過程會(huì)根據(jù)傳入的參數(shù)@docount來確定是不是要返回所有要分頁(yè)的記錄總數(shù)
特別是這兩行
set rowcount @PageUpperBound
insert into @indextable(nid) select id from luntan where this_id=@this_id order by reply_time desc
真的是妙不可言!!set rowcount @PageUpperBound當(dāng)記錄數(shù)達(dá)到@PageUpperBound時(shí)就會(huì)停止處理查詢
,select id 只把id列取出放到臨時(shí)表里,select a.* from luntan a,@indextable t where a.id=t.nid
and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id
而這句也只從表中取出所需要的記錄,而不是所有的記錄,結(jié)合起來,極大的提高了效率!!
妙啊,真的妙!!!!
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
聲明:本網(wǎng)頁(yè)內(nèi)容旨在傳播知識(shí),若有侵權(quán)等問題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系,我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com
本文如未解决您的问题请添加抖音号:51dongshi(抖音搜索懂视),直接咨询即可。