如何最好地重用相同的存储过程以获取详细信息和摘要

Question

给定如下存储过程:

create procedure BigParameterizedSearch(@criteria xml, @page int, @pageSize int)
as
    ...lots of populating table variables & transforming things...
    select ..bunch of columns..
    from   ..bunch of tables..
    where  ..bunch of (@has_some_filter=0 or ..some filter criteria..) and..
    order by ..big case statement depends on criteria..
    offset (@page-1)*@pageSize rows
    fetch next @pageSize rows only
    option (recompile)

和对应的'summary':

create procedure BigParameterizedSearchSummary(@criteria xml, @page int, @pageSize int)
as
    ...same exact populating table variables & transforming things...
    select groupCol, ..various aggregates..
    from   ..same exact bunch of tables..
    where  ..same exact bunch of (@has_some_filter=0 or ..some filter criteria..) and..
    group by groupCol
    order by ..smaller case statement depends on criteria..
    offset (@page-1)*@pageSize rows
    fetch next @pageSize rows only
    option (recompile)

两个存储过程基本相同，只有select子句和order by子句不同，并且"摘要"版本增加了group by。

现在的问题是，这两个存储过程是如何结合在一起的?或者，如何避免重复的代码?我尝试用returns table as return创建一个公共的表值函数，以便在不影响性能的情况下将select和group by提取出来调用存储过程，但没有成功。returns table as return上的限制使得执行所有复杂的设置非常困难，如果我让它填充一个表变量，那么将为每个页面填充完整的结果集，这会大大降低速度。除了完全动态SQL之外，还有其他策略吗?

Answer 1

您可以使用一个视图，该视图包含所有表中的所有列以及两个查询所需的所有过滤器，然后针对该视图进行选择和分组。如果我对你的情况理解正确的话，你就不用担心裁员的问题了。老实说，这类问题正是视图的作用所在。

还有，我只是好奇，你真的需要每次重新编译吗?是否发生了一些不可思议的事情，使您无法负担使用缓存执行计划的费用?

Answer 2

您是否可以将其拆分为填充表的sproc和使用相同参数调用表填充然后查询表的sproc ?

create table Prepop (a int)
go
create procedure uspPopulate (@StartNum int)
as
truncate table Prepop
insert into Prepop values
 (@StartNum)
,(@StartNum+1)
,(@StartNum+2)
,(@StartNum+3)
go
create procedure uspCall (@StartNum int, @Summary bit)
as
exec uspPopulate @StartNum = @StartNum
if @Summary = 1
    select avg(a) as Avga
    from Prepop
else
    select a
    from Prepop
go
exec uspCall @StartNum = 6, @Summary = 1
exec uspCall @StartNum = 6, @Summary = 0