您现在的位置是:首页 > SQLServer

SQLServer

临时表

2020-11-21 11:29:42 SQLServer admin
ALTER proc [dbo].[e_pro_cost]@type tinyint,@IsWhere nvarchar(1000)asbeginif exists(select * from tempdb.dbo.sysobjects w

ALTER proc [dbo].[e_pro_cost]
@type tinyint,
@IsWhere nvarchar(1000)
as
begin
if exists(select * from tempdb.dbo.sysobjects where id=object_id(N'tempdb.dbo.#t_equ_cost'))
drop table #t_equ_cost
create table #t_equ_cost
(
Batch varchar(50),
eCostPrice float,
MOID varchar(50),
Date Datetime,
PMCPlanner varchar(50),
PMCPlannerName varchar(50)
)
insert into #t_equ_cost exec e_equ_cost @type
end

--将人工信息存入临时表
begin
if exists(select * from tempdb.dbo.sysobjects where id=object_id(N'tempdb.dbo.#t_hum_cost'))
drop table #t_hum_cost
create table #t_hum_cost
(
Batch varchar(50),
hCostPrice float,
MOID varchar(50),
Date Datetime,
PMCPlanner varchar(50),
PMCPlannerName varchar(50)
)
insert into #t_hum_cost exec e_hum_cost @type
end

begin
if exists(select * from tempdb.dbo.sysobjects where id=object_id(N'tempdb.dbo.#t_met_cost'))
drop table #t_met_cost
create table #t_met_cost
(
Batch varchar(50),
mCostPrice float,
MOID varchar(50),
Date Datetime,
PMCPlanner varchar(50),
PMCPlannerName varchar(50)
)
insert into #t_met_cost exec e_met_cost @type
end

declare @sql varchar(2000)
set @sql='select *,(eCostPrice+hCostPrice+mCostPrice) as TotalCostPrice from (
select
isnull(A.Batch,B.Batch) as Batch,
isnull(A.MOID,B.MOID) as MOID,
isnull(A.Date,B.Date) as Date,
isnull(A.PMCPlanner,B.PMCPlanner) as PMCPlanner,
isnull(A.PMCPlannerName,B.PMCPlannerName) as PMCPlannerName,
isnull(A.eCostPrice,0) as eCostPrice,
isnull(A.hCostPrice,0) as hCostPrice,
isnull(B.mCostPrice,0) as mCostPrice from
(
select
isnull(A.Batch,B.Batch) as Batch,
isnull(A.MOID,B.MOID) as MOID,
isnull(A.Date,B.Date) as Date,
isnull(A.PMCPlanner,B.PMCPlanner) as PMCPlanner,
isnull(A.PMCPlannerName,B.PMCPlannerName) as PMCPlannerName,
A.eCostPrice,B.hCostPrice from #t_equ_cost A full outer join #t_hum_cost B on A.Batch=B.Batch
)
A full outer join #t_met_cost B on A.Batch=B.Batch
) A where '+@IsWhere;

execute (@sql)

drop table #t_met_cost
drop table #t_hum_cost
drop table #t_equ_cost
GO