分頁存儲過程，綜合了NOT_IN和SET ROWcount
2024-07-21 02:05:44
供稿：網友
分頁存儲過程，自己把“not_in”和“set rowcount”兩種方式綜合了一下。
“set rowcount”算法不變，“not_in”排序時加了主鍵字段，這樣速度提升了很多而且結果是單向唯一的，但不是雙向可逆的。“set rowcount”沒有速度問題，“not_in”排序加了主鍵字段后在30w記錄時對非索引字段、非聚合字段的排序翻一頁不過3秒鐘，還可以吧：p
－－－－－－－－－－－－－－－－－－－－－－
create procedure paging_custom
(
@tables varchar(1000),
@pk varchar(100),
@sort varchar(200) = null,
@isascending bit=1,
@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 @sort type*/
declare @strfilter varchar(1000)
declare @strsimplefilter varchar(1000)
declare @strgroup varchar(1000)
declare @sorttable varchar(100)
declare @sortname varchar(100)
declare @strpkcolumn varchar(200)
declare @strsortcolumn varchar(200)
declare @operator char(2)
/*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
begin
set @strgroup = ' '
end
/*count*/
exec('select count(*) from ' [email protected]+' '+ @strfilter)
/*set sorting variables.*/
set @strsortcolumn [email protected]
/*operator and asc_desc*/
declare @strasc_des varchar(10)
if @isascending = 0
begin
set @operator = '<='
set @strasc_des = ' desc '
end
else
begin
set @operator = '>='
set @strasc_des = ' asc '
end
if charindex('.', @pk) > 0
begin
set @strpkcolumn = substring(@pk, 0, charindex('.',@pk))
end
else
begin
set @strpkcolumn = @pk
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
/*handler complex table*/
--be join table,so get the left table
if charindex(' join ',@tables) > 0
begin
set @sorttable=substring(ltrim(@tables),0,charindex(' ',@tables))
set @strsortcolumn = @sorttable+'.'[email protected]
end
/*check the sortcolumn if be unique*/
declare @tempname varchar(100)
if @sortname <> @strpkcolumn
begin
select @tempname=b.name
from sysobjects a inner join
sysobjects b on a.id = b.parent_obj
inner join sysindexes c on b.name = c.name inner join
sysindexkeys d on c.id = d.id and c.indid = d.indid inner join
syscolumns e on d.id = e.id and d.colid = e.colid
where (b.xtype = 'uq') and (a.name = @sorttable) and (e.name = @sortname)
if @tempname is null goto:paging_not_in
end
declare @type varchar(100)
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
/*the left table doesn't contain sortcolumn */
--if @type is null or @type='' goto:paging_not_in
if charindex('char', @type) > 0
set @type = @type + '(' + cast(@prec as varchar) + ')'
declare @strpagesize varchar(50)
declare @strstartrow varchar(50)
/*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))
/*execute dynamic query*/
exec( 'declare @sortcolumn ' + @type + '
set rowcount ' + @strstartrow +
'select @sortcolumn=' + @strsortcolumn + ' from ' + @tables + @strfilter + ' ' + @strgroup +
' order by ' + @sort + @strasc_des+'set rowcount ' + @strpagesize +
'select ' + @fields + ' from ' + @tables + ' where ' + @strsortcolumn + @operator + ' @sortcolumn ' +
@strsimplefilter + ' ' + @strgroup + ' order by ' + @sort + @strasc_des
)
return
paging_not_in:
--declare @strpagesize varchar(50)
--set @strpagesize = cast(@pagesize as varchar(50))
declare @strtotalnum int
set @strtotalnum = (@pagenumber - 1)*@pagesize
--第一頁
--declare @strsql varchar(8000)
if @strtotalnum = 0
begin
exec('select top '[email protected]+' '[email protected]+' from '[email protected]+' '[email protected]+ @strgroup + ' order by ' + @sort + @strasc_des)
end
else
begin
exec('select top '[email protected]+' '[email protected]+' from '[email protected]+' where '
[email protected]+' not in(select top '
[email protected]+' '[email protected]+' from '[email protected]+' '[email protected]+ @strgroup +
' order by ' + @sort + @strasc_des
+') '[email protected]+ @strgroup + ' order by ' + @sort + @strasc_des)
end
go
