下面是我的SQL SERVER 存储过程
生成的临时表#tempprod想在DW里显示，不知道怎样做
if not exists(select * from tempdb..sysobjects where id=object_id( 'tempdb..#tempprod '))
create table #tempprod(prod_no char(8) primary key,prod_name char(50),prod_size char(20),last_add char(12),monad char(4),prod_price_db numeric(9,2),corp_price numeric(9,2),lest_num numeric(9,3),ml numeric(9,2))
else
delete from #tempprod
declare @myprodno char(8),@myprodname char(50) ,@myprodsize char(20),@mylastadd char(12)
declare @mymonad char(4),@myprodpricedb numeric(9,2)
declare @mycorpprice numeric(9,2),@ml numeric(9,2)
declare @ml1 numeric(9,2),@ml2 numeric(9,2),@formvalue1 numeric(9,2),@formvalue2 numeric(9,2)
declare @formvalue3 numeric(9,2),@formvalue4 numeric(9,2),@formvalue5 numeric(9,2),@formvalue6 numeric(9,2)
declare @rndmycorpprice numeric(9,2),@mylestnum numeric(9,2)
declare cur_prod cursor for select p.prod_no,prod_name,prod_size,last_add,monad,prod_price_db,corp_price,c.lest_num from product p left join prod_dep c on p.prod_no=c.prod_no
open cur_prod
fetch next from cur_prod into @myprodno,@myprodname,@myprodsize,@mylastadd,@mymonad,@myprodpricedb,@mycorpprice,@mylestnum
while @@fetch_status=0
begin
if @myprodpricedb is null
set @myprodpricedb=0
if @mycorpprice is null
set @mycorpprice=0
if @mylestnum is null
set @mylestnum=0
set @formvalue1=@myprodpricedb-@mycorpprice/1.35
set @formvalue2=@myprodpricedb-(@mycorpprice-0.5)/1.3
set @formvalue3=@myprodpricedb-(@mycorpprice-4.5)/1.2
set @formvalue4=@myprodpricedb-(@mycorpprice-14.5)/1.1
set @formvalue5=@myprodpricedb-(@mycorpprice-24.5)/1.08
set @formvalue6=@myprodpricedb-(@mycorpprice-184.5)
set @rndmycorpprice=round(@mycorpprice,2)
if (@rndmycorpprice>=0 and @rndmycorpprice<=17.40) or (@rndmycorpprice>=17.51 and @rndmycorpprice<=63.30) or (@rndmycorpprice>=64.21 and @rndmycorpprice<=265.29) or (@rndmycorpprice>=284.11 and @rndmycorpprice<=743.78) or (@rndmycorpprice>=749.31 and @rndmycorpprice<=2362.49) or @rndmycorpprice>=2376.75
begin
if @rndmycorpprice>=0 and @rndmycorpprice<=17.40
set @[email protected]
if @rndmycorpprice>=17.51 and @rndmycorpprice<=63.30
set @[email protected]
if @rndmycorpprice>=64.21 and @rndmycorpprice<=265.29
set @[email protected]
if @rndmycorpprice>=284.11 and @rndmycorpprice<=743.78
set @[email protected]
if @rndmycorpprice>=749.31 and @rndmycorpprice<=2362.49
set @[email protected]
if @rndmycorpprice>=2376.75
set @[email protected]
end
else
begin
if @rndmycorpprice>=17.41 and @rndmycorpprice<=17.50
begin
set @[email protected]
set @[email protected]
end
if @rndmycorpprice>=63.31 and @rndmycorpprice<=64.20
begin
set @[email protected]
set @[email protected]
end
if @rndmycorpprice>=265.30 and @rndmycorpprice<=284.10
begin
set @[email protected]
set @[email protected]
end
if @rndmycorpprice>=743.79 and @rndmycorpprice<=749.30
begin
set @[email protected]
set @[email protected]