当前位置： 移动技术网 > IT编程>数据库>MSSQL > sqlserver 支持定位当前页,自定义排序的分页SQL(拒绝动态SQL)

sqlserver 支持定位当前页,自定义排序的分页SQL(拒绝动态SQL)

2017年12月12日  | 移动技术网IT编程  | 我要评论

李岳苍有多少钱,香港苹果官网中文,诺扬汗蒸房

1,场景：根据学生编号查询，返回该学生所在班级的所有学生。支持分页、自定义排序及结果集自动定位到查询条件的学生编号所在页。
复制代码 代码如下:

create proc [dbo].[sp_testpagerandsorting] (@groupid int,
@currentid int,
@timefrom datetime,
@timeto datetime,
@orderby char(50),
@pagesize int,
@currentpage int)
as
set nocount on
begin
declare @startnumber int,
@endnumber int,
@currentidrownumber int,
@recordcount int,
@endpageindex int
declare @rownumbertable table (
rownumber int identity (1, 1),
id int )
--step 1: build sort id list -------------------------------------------------------
insert into @rownumbertable
(id)
select sm.id as id
from dbo.test sm with (nolock)
where indate between coalesce(@timefrom, indate) and
coalesce(@timeto, indate)
and sm.groupid = @groupid
order by case
when @orderby = 'indate desc' then ( row_number() over (order by indate desc))
when @orderby = 'indate asc' then (row_number() over (order by indate asc))
when @orderby = 'id asc' then (row_number() over (order by sm.id asc))
when @orderby = 'id desc' then (row_number() over (order by sm.id desc))
when @orderby = 'name asc' then (row_number() over (order by sm.name asc))
when @orderby = 'name desc' then (row_number() over (order by sm.name desc) )
end
--step 2: reset page index with current id -----------------------------------------
if @currentidnumber > 0
begin
select top 1 @currentidrownumber = rownumber
from @rownumbertable
where id = @currentidnumber
if @currentidrownumber > 0
begin
if @currentpage = 0
begin
set @currentpage = ceiling(cast(@currentidrownumber as decimal) / cast (@pagesize as decimal))
end
end
end
else
begin
if @currentpage = 0
begin
set @currentpage = 1
end
end
--step 3: set recordcount -----------------------------------------
select @recordcount = count(1)
from @rownumbertable
--step 4: calc startnumber & endnumber -----------------------------------------
select @startnumber = @pagesize * ( @currentpage - 1 ),
@endnumber = @pagesize * ( @currentpage - 1 ) + @pagesize,
@endpageindex = ceiling(cast(@recordcount as decimal) / cast(@pagesize as decimal))
if @currentpage = @endpageindex
begin
set @endnumber = @recordcount
end
--step 5: get sorted id of current page -----------------------------------------
;with a
as (select top (@endnumber - @startnumber) id,
rownumber
from (select top (@endnumber) id,
rownumber
from @rownumbertable) as b
order by rownumber desc)
--step 6: return current page idlist -------------------------------------------------------
select [id],
[groupid] [name],
[address]
from dbo.test sm with(nolock)
inner join a
on a.id = sm.id
order by a.rownumber
-- step 7:return current page & record count ----------------------------------
select @currentpage as currentpage,
@recordcount as recordcount
end

2,简单条件的,动态where语句(关于like查询的动态where，建议使用笨办法做)
复制代码 代码如下:

create proc [dbo].[getstudentlistbycondition] @name nvarchar(20),
@class int
as
set nocount on
begin
begin
select [name],
[class]
from [testtable]
where [class] = case
when @class > 0 then @class else [class] end
and [name] = case
when @name <> '' then @name else [name] end
end
end

您可能感兴趣的文章:

如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网