使用临时表(存储过程)

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

/*
   作用：获取基金最新盈亏情况                
   作者：captain
    时间：2008.05.28
*/

ALTER             PROCEDURE AA_sp_FundGetNewMsg
(
@UserId int=0,    --用户编号
@CombId int=0    --组合编号
)
AS

if(@UserId<>0)
  --组合为我的组合
  begin
    if object_id('tempdb..#t') is not null
    drop table #t

    if object_id('tempdb..#t2') is not null
    drop table #t

    if object_id('tempdb..#t3') is not null
    drop table #t
 --获得临时表#t
 select a.*,b.JCode,b.FundName into #t from BM_FCombWinTbl a inner join BM_FCombDetailTbl b
    on a.DetailId=b.DetailId
    where b.CombId in(select CombId from BM_FundCombTbl
     where CombType=101201 and UserId=@UserId)

 --获得临时表#t2
 select #t.* into #t2 from #t,(select jcode,max(wdate) as wdate from #t group by jcode) b where #t.jcode=b.jcode and #t.wdate=b.wdate

 --获得临时表#t3
 select #t2.*,vv.NetVal,vv.DayAddPer into #t3 from #t2 INNER JOIN
    BM_F_NetVTbl vv ON #t2.JCode = vv.JCode and #t2.wdate=vv.ddate

 --获取结果
 select * from  #t3 where id in(select max(id) from #t3 group by WDate,JCode) order by WDate,id asc

  end
else
  begin                                               
    if object_id('tempdb..#f') is not null
    drop table #f

    if object_id('tempdb..#f2') is not null
    drop table #f

    if object_id('tempdb..#f3') is not null
    drop table #f
 --获得临时表#f
 select a.*,b.JCode,b.FundName into #f from BM_FCombWinTbl a inner join BM_FCombDetailTbl b
    on a.DetailId=b.DetailId
    where b.CombId=@CombId

 --获得临时表#f2
 select #f.* into #f2 from #f,(select jcode,max(wdate) as wdate from #f group by jcode) b where #f.jcode=b.jcode and #f.wdate=b.wdate

 --获得临时表#f3
 select #f2.*,vv.NetVal,vv.DayAddPer into #f3 from #f2 INNER JOIN
    BM_F_NetVTbl vv ON #f2.JCode = vv.JCode and #f2.wdate=vv.ddate

 --获取结果
 select * from  #f3 where id in(select max(id) from #f3 group by WDate,JCode) order by WDate,id asc
  end