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