临时表--.net学习网

临时表

创建时间：2011年06月09日 20:39 阅读次数：(5183)

分享到：

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;