SQL 存储过程调用其他存储过程的表

有一个存储过程是统计司机节油的。分部分和副司机查询 4中情况，还有司机担当副司机的查询，2种情况。一个6种情况

每种情况都类似，考虑另外封装存储过程来简化。

ALTER PROCEDURE [dbo].[driverSaveOilSub]
	-- Add the parameters for the stored procedure here
	@startdate datetime,
	@enddate datetime,
	@department  varchar(5),
	@driverDoAsistant bit =0 --司机担当副司机，默认司机不到担当。传入其他值的时候，是担当的情况
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;----

	 select DriverGh, 0.5* sum(dzCount+dxCount+dcCount)+sum(score) as driverValue,
	  count( distinct tb_driverReportInput.submitDate) as  driverReportCount,
	  CAST(sum(tb_driverReportDetails.comprehensiveOil) as decimal(18,2))as comprehensiveOil
  from  tb_driverReportDetails left join tb_driverReportInput on tb_driverReportInput.id=tb_driverReportDetails.reportid
	left join tb_stationMileage on tb_stationMileage.id=mileageStationId  
   where (select department from tb_driver where tb_driver.gh=(CASE when @driverDoAsistant=0 then DriverGh else assistantDriverGh end) and post='司机')=@department
   and fromDateTime>=@startdate and fromDateTime<=@enddate  group by DriverGh
END

------------下面是整个存储过程

先创建一些临时表，临时标的类型和 名称要起好。

然后调用子存储过程向临时表填充数据。用这个表就没问题。最后临时表的删除代码没贴上。

还有很多重复的。用sql 拼接？还没试。

ALTER PROCEDURE [dbo].[driverSaveOil]
	-- Add the parameters for the stored procedure here
	@startdate datetime,
	@enddate datetime
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	--set @startdate=dateadd(hour,18,@startdate)
	--set @enddate=dateadd(hour,18,@enddate)
    -- Insert statements for procedure here
if OBJECT_ID('tempdb..tianjiDriver') is not null
drop table #tianjiDriver


if OBJECT_ID('tempdb..xieqiaoDriver') is not null
drop table #xieqiaoDriver

if OBJECT_ID('tempdb..tianjiAssistant') is not null
drop table #tianjiAssistant

if OBJECT_ID('tempdb..xieqiaoAssistant') is not null
drop table #xieqiaoAssistant

if OBJECT_ID('tempdb..xieqiaoDriverDoAsistant') is not null
drop table #xieqiaoDriverDoAsistant

if OBJECT_ID('tempdb..tianjiDriverDoAsistant') is not null
drop table #tianjiDriverDoAsistant

CREATE TABLE [dbo].[#tianjiDriver](
	[DriverGh] [nvarchar](20),
	[driverValue] float,
	[driverReportCount] int,
	[comprehensiveOil] decimal(18,2) NULL
) 
CREATE TABLE [dbo].[#xieqiaoDriver](
	[DriverGh] [nvarchar](20),
	[driverValue] float,
	[driverReportCount] int,
	[comprehensiveOil] decimal(18,2) NULL
) 
--
CREATE TABLE [dbo].[#tianjiDriverDoAsistant](
	[DriverGh] [nvarchar](20),
	[driverValue] float,
	[driverReportCount] int,
	[comprehensiveOil] decimal(18,2) NULL
) 
CREATE TABLE [dbo].[#xieqiaoDriverDoAsistant](
	[DriverGh] [nvarchar](20),
	[driverValue] float,
	[driverReportCount] int,
	[comprehensiveOil] decimal(18,2) NULL
) --


CREATE TABLE [dbo].[#tianjiAssistant](
	[assistantDriverGh] [nvarchar](20),
	[driverValue] float,
	[driverReportCount] int,
	[comprehensiveOil] decimal(18,2) NULL
) 
CREATE TABLE #xieqiaoAssistant(
	[assistantDriverGh] [nvarchar](20),
	[driverValue] float,
	[driverReportCount] int,
	[comprehensiveOil] decimal(18,2) NULL
) 




INSERT #tianjiDriver      EXEC   [dbo].[driverSaveOilSub] @startdate,@enddate,'田集'
INSERT #tianjiAssistant EXEC   [dbo].[driverSaveOilSubAsistant]  @startdate,@enddate,'田集'
INSERT #xieqiaoDriver    EXEC  [dbo].[driverSaveOilSub] @startdate,@enddate,'谢桥'
INSERT #xieqiaoAssistant EXEC  [dbo].[driverSaveOilSubAsistant] @startdate,@enddate,'谢桥'

INSERT #tianjiDriverDoAsistant EXEC   [dbo].[driverSaveOilSub] @startdate,@enddate,'田集',1
INSERT #xieqiaoDriverDoAsistant EXEC  [dbo].[driverSaveOilSub] @startdate,@enddate,'谢桥',1

   select  
   (select gh from tb_driver where gh=DriverGh) as  gh, 
   (select drivername from tb_driver where gh=DriverGh) as  name, 
   (select department from tb_driver where gh=DriverGh) as  department, 
   (select post from tb_driver where gh=DriverGh) as  post,
	 '司机' as  workpost, 
                    CAST((select sum(actualOil) from tb_driverReportInput nb where nb.DriverGh=b.DriverGh and fromDateTime>=@startdate and fromDateTime<=@enddate) as decimal(18,2)) as actualOil,
CAST(b.comprehensiveOil -(select sum(actualOil) from tb_driverReportInput nb where nb.DriverGh=b.DriverGh and fromDateTime>=@startdate and fromDateTime<=@enddate) as decimal(18,2)) as saveOil,
	 * from 
	#tianjiDriver  b


   union all


  select  
     (select gh from tb_driver where gh=assistantDriverGh) as  gh, 
   (select drivername from tb_driver where gh=assistantDriverGh) as  name, 
   (select department from tb_driver where gh=assistantDriverGh) as  department, 
    (select post from tb_driver where gh=assistantDriverGh) as  post,
	 '副司机' as  workpost, 
	                CAST((select sum(actualOil) from tb_driverReportInput nb where nb.assistantDriverGh=assistantDriverGh and fromDateTime>=@startdate and fromDateTime<=@enddate)as decimal(18,2))as actualOil,
CAST(comprehensiveOil -(select sum(actualOil) from tb_driverReportInput nb where nb.assistantDriverGh=assistantDriverGh and fromDateTime>=@startdate and fromDateTime<=@enddate) as decimal(18,2))as saveOil,
	 * from 
	[#tianjiAssistant]


   union all 
    select  
   (select gh from tb_driver where gh=DriverGh) as  gh, 
   (select drivername from tb_driver where gh=DriverGh) as  name, 
   (select department from tb_driver where gh=DriverGh) as  department, 
    (select post from tb_driver where gh=DriverGh) as  post,
	 '司机' as  workpost, 
	               CAST((select sum(actualOil) from tb_driverReportInput nb where nb.DriverGh=DriverGh and fromDateTime>=@startdate and fromDateTime<=@enddate)  as decimal(18,2))as actualOil,
 CAST(comprehensiveOil- (select sum(actualOil) from tb_driverReportInput nb where nb.DriverGh=DriverGh  and fromDateTime>=@startdate and fromDateTime<=@enddate) as decimal(18,2))as saveOil,
	 * from 
	[#xieqiaoDriver]
   union all


  	select  
	(select gh from tb_driver where gh=assistantDriverGh)   as  gh, 
    (select drivername from tb_driver where gh=assistantDriverGh) as  name, 
    (select department from tb_driver where gh=assistantDriverGh) as  department, 
    (select post from tb_driver where gh=assistantDriverGh) as  post,
	 '副司机' as  workpost, 
	               CAST((select sum(actualOil) from tb_driverReportInput nb where nb.assistantDriverGh=assistantDriverGh and fromDateTime>=@startdate and fromDateTime<=@enddate) as decimal(18,2))as actualOil,
  CAST(comprehensiveOil-(select sum(actualOil) from tb_driverReportInput nb where nb.assistantDriverGh=assistantDriverGh and fromDateTime>=@startdate and fromDateTime<=@enddate) as decimal(18,2))as saveOil,
	 * from 
[#xieqiaoAssistant]
-------------------------
  union all 
    select  
   (select gh from tb_driver where gh=DriverGh) as  gh, 
   (select drivername from tb_driver where gh=DriverGh) as  name, 
   (select department from tb_driver where gh=DriverGh) as  department, 
    (select post from tb_driver where gh=DriverGh) as  post,
	 '副司机' as  workpost, 
	               CAST((select sum(actualOil) from tb_driverReportInput nb where nb.DriverGh=DriverGh and fromDateTime>=@startdate and fromDateTime<=@enddate)  as decimal(18,2))as actualOil,
CAST(comprehensiveOil-(select sum(actualOil) from tb_driverReportInput nb where nb.DriverGh=DriverGh  and fromDateTime>=@startdate and fromDateTime<=@enddate) as decimal(18,2))as saveOil,
	 * from 
#tianjiDriverDoAsistant


  union all 
    select  
   (select gh from tb_driver where gh=DriverGh) as  gh, 
   (select drivername from tb_driver where gh=DriverGh) as  name, 
   (select department from tb_driver where gh=DriverGh) as  department, 
    (select post from tb_driver where gh=DriverGh) as  post,
	 '副司机' as  workpost, 
	               CAST((select sum(actualOil) from tb_driverReportInput nb where nb.DriverGh=DriverGh and fromDateTime>=@startdate and fromDateTime<=@enddate)  as decimal(18,2))as actualOil,
  CAST(comprehensiveOil-(select sum(actualOil) from tb_driverReportInput nb where nb.DriverGh=DriverGh  and fromDateTime>=@startdate and fromDateTime<=@enddate) as decimal(18,2))as saveOil,
	 * from 
	#xieqiaoDriverDoAsistant
END