CSDN博客

img wjscsdn

最长的procedure

发表于2004/10/15 14:49:00  573人阅读

分类: C#

CREATE PROCEDURE dbo.Money_SelectSumInfoByServerId_ffokadmin
@ServerId int
AS
--------------------------------
--------------------------------
DECLARE @Hou MONEY
DECLARE @Yang MONEY
DECLARE @Ma MONEY
DECLARE @She MONEY
DECLARE @Long MONEY
DECLARE @Tu MONEY
DECLARE @Hu MONEY
DECLARE @Niu MONEY
DECLARE @Shu MONEY
DECLARE @Zhu MONEY
DECLARE @Gou MONEY
DECLARE @Ji MONEY
DECLARE @Dan MONEY
DECLARE @Shuang MONEY
DECLARE @Da MONEY
DECLARE @Xiao MONEY
DECLARE @N1 MONEY
DECLARE @N2 MONEY
DECLARE @N3 MONEY
DECLARE @N4 MONEY
DECLARE @N5 MONEY
DECLARE @N6 MONEY
DECLARE @N7 MONEY
DECLARE @N8 MONEY
DECLARE @N9 MONEY
DECLARE @N10 MONEY
DECLARE @N11 MONEY
DECLARE @N12 MONEY
DECLARE @N13 MONEY
DECLARE @N14 MONEY
DECLARE @N15 MONEY
DECLARE @N16 MONEY
DECLARE @N17 MONEY
DECLARE @N18 MONEY
DECLARE @N19 MONEY
DECLARE @N20 MONEY
DECLARE @N21 MONEY
DECLARE @N22 MONEY
DECLARE @N23 MONEY
DECLARE @N24 MONEY
DECLARE @N25 MONEY
DECLARE @N26 MONEY
DECLARE @N27 MONEY
DECLARE @N28 MONEY
DECLARE @N29 MONEY
DECLARE @N30 MONEY
DECLARE @N31 MONEY
DECLARE @N32 MONEY
DECLARE @N33 MONEY
DECLARE @N34 MONEY
DECLARE @N35 MONEY
DECLARE @N36 MONEY
DECLARE @N37 MONEY
DECLARE @N38 MONEY
DECLARE @N39 MONEY
DECLARE @N40 MONEY
DECLARE @N41 MONEY
DECLARE @N42 MONEY
DECLARE @N43 MONEY
DECLARE @N44 MONEY
DECLARE @N45 MONEY
DECLARE @N46 MONEY
DECLARE @N47 MONEY
DECLARE @N48 MONEY
DECLARE @N49 MONEY
--------------------------------
--------------------------------
select @Hou=SUM(Hou),@Yang=SUM(Yang),@Ma=SUM(Ma),@She=SUM(She),@Long=SUM(Long),@Tu=SUM(Tu),@Hu=SUM(Hu),@Niu=SUM(Niu),@Shu=SUM(Shu),@Zhu=SUM(Zhu),@Gou=SUM(Gou),@Ji=SUM(Ji),@Dan=SUM(Dan),@Shuang=SUM(Shuang),@Da=SUM(Da),@Xiao=SUM(Xiao),@N1=SUM(N1),@N2=SUM(N2),@N3=SUM(N3),@N4=SUM(N4),@N5=SUM(N5),@N6=SUM(N6),@N7=SUM(N7),@N8=SUM(N8),@N9=SUM(N9),@N10=SUM(N10),@N11=SUM(N11),@N12=SUM(N12),@N13=SUM(N13),@N14=SUM(N14),@N15=SUM(N15),@N16=SUM(N16),@N17=SUM(N17),@N18=SUM(N18),@N19=SUM(N19),@N20=SUM(N20),@N21=SUM(N21),@N22=SUM(N22),@N23=SUM(N23),@N24=SUM(N24),@N25=SUM(N25),@N26=SUM(N26),@N27=SUM(N27),@N28=SUM(N28),@N29=SUM(N29),@N30=SUM(N30),@N31=SUM(N31),@N32=SUM(N32),@N33=SUM(N33),@N34=SUM(N34),@N35=SUM(N35),@N36=SUM(N36),@N37=SUM(N37),@N38=SUM(N38),@N39=SUM(N39),@N40=SUM(N40),@N41=SUM(N41),@N42=SUM(N42),@N43=SUM(N43),@N44=SUM(N44),@N45=SUM(N45),@N46=SUM(N46),@N47=SUM(N47),@N48=SUM(N48),@N49=SUM(N49) from Money m
join
(select id from [User] where agent = 'ffokadmin') u
on
m.UserId = u.ID
where ServerId=@ServerId
--------------------------------
--------------------------------
DECLARE @agent_idx varchar(40) -- 记录代理名称
DECLARE @agent_tel float -- 记录股份数
DECLARE Agent_Cursor CURSOR FOR
select distinct agent from [user] where id in (select distinct userid from money where ServerId=@ServerId) and agent <> 'ffokadmin'
OPEN Agent_Cursor
----------------------------------------------
-- - - - - - - - - - - - - - - - - - - - - - -
set @agent_idx = '' -- 置空
FETCH NEXT FROM Agent_Cursor INTO @agent_idx

if @agent_idx <> ''
BEGIN -- begin if
 set @agent_tel = (select tel from agent where idx = @agent_idx) -- 得到股份数
 --
 -- 计算总额
 --
 select @Hou=SUM(Hou)*(1-@agent_tel)+@Hou,@Yang=SUM(Yang)*(1-@agent_tel)+@Yang,@Ma=SUM(Ma)*(1-@agent_tel)+@Ma,@She=SUM(She)*(1-@agent_tel)+@She,@Long=SUM(Long)*(1-@agent_tel)+@Long,@Tu=SUM(Tu)*(1-@agent_tel)+@Tu,@Hu=SUM(Hu)*(1-@agent_tel)+@Hu,@Niu=SUM(Niu)*(1-@agent_tel)+@Niu,@Shu=SUM(Shu)*(1-@agent_tel)+@Shu,@Zhu=SUM(Zhu)*(1-@agent_tel)+@Zhu,@Gou=SUM(Gou)*(1-@agent_tel)+@Gou,@Ji=SUM(Ji)*(1-@agent_tel)+@Ji,@Dan=SUM(Dan)*(1-@agent_tel)+@Dan,@Shuang=SUM(Shuang)*(1-@agent_tel)+@Shuang,@Da=SUM(Da)*(1-@agent_tel)+@Da,@Xiao=SUM(Xiao)*(1-@agent_tel)+@Xiao,@N1=SUM(N1)*(1-@agent_tel)+@N1,@N2=SUM(N2)*(1-@agent_tel)+@N2,@N3=SUM(N3)*(1-@agent_tel)+@N3,@N4=SUM(N4)*(1-@agent_tel)+@N4,@N5=SUM(N5)*(1-@agent_tel)+@N5,@N6=SUM(N6)*(1-@agent_tel)+@N6,@N7=SUM(N7)*(1-@agent_tel)+@N7,@N8=SUM(N8)*(1-@agent_tel)+@N8,@N9=SUM(N9)*(1-@agent_tel)+@N9,@N10=SUM(N10)*(1-@agent_tel)+@N10,@N11=SUM(N11)*(1-@agent_tel)+@N11,@N12=SUM(N12)*(1-@agent_tel)+@N12,@N13=SUM(N13)*(1-@agent_tel)+@N13,@N14=SUM(N14)*(1-@agent_tel)+@N14,@N15=SUM(N15)*(1-@agent_tel)+@N15,@N16=SUM(N16)*(1-@agent_tel)+@N16,@N17=SUM(N17)*(1-@agent_tel)+@N17,@N18=SUM(N18)*(1-@agent_tel)+@N18,@N19=SUM(N19)*(1-@agent_tel)+@N19,@N20=SUM(N20)*(1-@agent_tel)+@N20,@N21=SUM(N21)*(1-@agent_tel)+@N21,@N22=SUM(N22)*(1-@agent_tel)+@N22,@N23=SUM(N23)*(1-@agent_tel)+@N23,@N24=SUM(N24)*(1-@agent_tel)+@N24,@N25=SUM(N25)*(1-@agent_tel)+@N25,@N26=SUM(N26)*(1-@agent_tel)+@N26,@N27=SUM(N27)*(1-@agent_tel)+@N27,@N28=SUM(N28)*(1-@agent_tel)+@N28,@N29=SUM(N29)*(1-@agent_tel)+@N29,@N30=SUM(N30)*(1-@agent_tel)+@N30,@N31=SUM(N31)*(1-@agent_tel)+@N31,@N32=SUM(N32)*(1-@agent_tel)+@N32,@N33=SUM(N33)*(1-@agent_tel)+@N33,@N34=SUM(N34)*(1-@agent_tel)+@N34,@N35=SUM(N35)*(1-@agent_tel)+@N35,@N36=SUM(N36)*(1-@agent_tel)+@N36,@N37=SUM(N37)*(1-@agent_tel)+@N37,@N38=SUM(N38)*(1-@agent_tel)+@N38,@N39=SUM(N39)*(1-@agent_tel)+@N39,@N40=SUM(N40)*(1-@agent_tel)+@N40,@N41=SUM(N41)*(1-@agent_tel)+@N41,@N42=SUM(N42)*(1-@agent_tel)+@N42,@N43=SUM(N43)*(1-@agent_tel)+@N43,@N44=SUM(N44)*(1-@agent_tel)+@N44,@N45=SUM(N45)*(1-@agent_tel)+@N45,@N46=SUM(N46)*(1-@agent_tel)+@N46,@N47=SUM(N47)*(1-@agent_tel)+@N47,@N48=SUM(N48)*(1-@agent_tel)+@N48,@N49=SUM(N49)*(1-@agent_tel)+@N49 from Money m
 join
 (select id from [User] where agent = @agent_idx) u
 on
 m.UserId = u.ID
 where ServerId=@ServerId
END -- end if


WHILE @@FETCH_STATUS = 0
BEGIN -- begin while
 set @agent_idx = '' -- 置空
 FETCH NEXT FROM Agent_Cursor INTO @agent_idx

 if @agent_idx <> ''
 BEGIN -- begin if
  set @agent_tel = (select tel from agent where idx = @agent_idx) -- 得到股份数
  --
  -- 计算总额
  --
  select @Hou=SUM(Hou)*(1-@agent_tel)+@Hou,@Yang=SUM(Yang)*(1-@agent_tel)+@Yang,@Ma=SUM(Ma)*(1-@agent_tel)+@Ma,@She=SUM(She)*(1-@agent_tel)+@She,@Long=SUM(Long)*(1-@agent_tel)+@Long,@Tu=SUM(Tu)*(1-@agent_tel)+@Tu,@Hu=SUM(Hu)*(1-@agent_tel)+@Hu,@Niu=SUM(Niu)*(1-@agent_tel)+@Niu,@Shu=SUM(Shu)*(1-@agent_tel)+@Shu,@Zhu=SUM(Zhu)*(1-@agent_tel)+@Zhu,@Gou=SUM(Gou)*(1-@agent_tel)+@Gou,@Ji=SUM(Ji)*(1-@agent_tel)+@Ji,@Dan=SUM(Dan)*(1-@agent_tel)+@Dan,@Shuang=SUM(Shuang)*(1-@agent_tel)+@Shuang,@Da=SUM(Da)*(1-@agent_tel)+@Da,@Xiao=SUM(Xiao)*(1-@agent_tel)+@Xiao,@N1=SUM(N1)*(1-@agent_tel)+@N1,@N2=SUM(N2)*(1-@agent_tel)+@N2,@N3=SUM(N3)*(1-@agent_tel)+@N3,@N4=SUM(N4)*(1-@agent_tel)+@N4,@N5=SUM(N5)*(1-@agent_tel)+@N5,@N6=SUM(N6)*(1-@agent_tel)+@N6,@N7=SUM(N7)*(1-@agent_tel)+@N7,@N8=SUM(N8)*(1-@agent_tel)+@N8,@N9=SUM(N9)*(1-@agent_tel)+@N9,@N10=SUM(N10)*(1-@agent_tel)+@N10,@N11=SUM(N11)*(1-@agent_tel)+@N11,@N12=SUM(N12)*(1-@agent_tel)+@N12,@N13=SUM(N13)*(1-@agent_tel)+@N13,@N14=SUM(N14)*(1-@agent_tel)+@N14,@N15=SUM(N15)*(1-@agent_tel)+@N15,@N16=SUM(N16)*(1-@agent_tel)+@N16,@N17=SUM(N17)*(1-@agent_tel)+@N17,@N18=SUM(N18)*(1-@agent_tel)+@N18,@N19=SUM(N19)*(1-@agent_tel)+@N19,@N20=SUM(N20)*(1-@agent_tel)+@N20,@N21=SUM(N21)*(1-@agent_tel)+@N21,@N22=SUM(N22)*(1-@agent_tel)+@N22,@N23=SUM(N23)*(1-@agent_tel)+@N23,@N24=SUM(N24)*(1-@agent_tel)+@N24,@N25=SUM(N25)*(1-@agent_tel)+@N25,@N26=SUM(N26)*(1-@agent_tel)+@N26,@N27=SUM(N27)*(1-@agent_tel)+@N27,@N28=SUM(N28)*(1-@agent_tel)+@N28,@N29=SUM(N29)*(1-@agent_tel)+@N29,@N30=SUM(N30)*(1-@agent_tel)+@N30,@N31=SUM(N31)*(1-@agent_tel)+@N31,@N32=SUM(N32)*(1-@agent_tel)+@N32,@N33=SUM(N33)*(1-@agent_tel)+@N33,@N34=SUM(N34)*(1-@agent_tel)+@N34,@N35=SUM(N35)*(1-@agent_tel)+@N35,@N36=SUM(N36)*(1-@agent_tel)+@N36,@N37=SUM(N37)*(1-@agent_tel)+@N37,@N38=SUM(N38)*(1-@agent_tel)+@N38,@N39=SUM(N39)*(1-@agent_tel)+@N39,@N40=SUM(N40)*(1-@agent_tel)+@N40,@N41=SUM(N41)*(1-@agent_tel)+@N41,@N42=SUM(N42)*(1-@agent_tel)+@N42,@N43=SUM(N43)*(1-@agent_tel)+@N43,@N44=SUM(N44)*(1-@agent_tel)+@N44,@N45=SUM(N45)*(1-@agent_tel)+@N45,@N46=SUM(N46)*(1-@agent_tel)+@N46,@N47=SUM(N47)*(1-@agent_tel)+@N47,@N48=SUM(N48)*(1-@agent_tel)+@N48,@N49=SUM(N49)*(1-@agent_tel)+@N49 from Money m
  join
  (select id from [User] where agent = @agent_idx) u
  on
  m.UserId = u.ID
  where ServerId=@ServerId
 END -- end if

END -- end while
-- - - - - - - - - - - - - - - - - - - - - - -
----------------------------------------------
CLOSE Agent_Cursor
DEALLOCATE Agent_Cursor
--------------------------------
--------------------------------
select Hou=@Hou,Yang=@Yang,Ma=@Ma,She=@She,Long=@Long,Tu=@Tu,Hu=@Hu,Niu=@Niu,Shu=@Shu,Zhu=@Zhu,Gou=@Gou,Ji=@Ji,Dan=@Dan,Shuang=@Shuang,Da=@Da,Xiao=@Xiao,N1=@N1,N2=@N2,N3=@N3,N4=@N4,N5=@N5,N6=@N6,N7=@N7,N8=@N8,N9=@N9,N10=@N10,N11=@N11,N12=@N12,N13=@N13,N14=@N14,N15=@N15,N16=@N16,N17=@N17,N18=@N18,N19=@N19,N20=@N20,N21=@N21,N22=@N22,N23=@N23,N24=@N24,N25=@N25,N26=@N26,N27=@N27,N28=@N28,N29=@N29,N30=@N30,N31=@N31,N32=@N32,N33=@N33,N34=@N34,N35=@N35,N36=@N36,N37=@N37,N38=@N38,N39=@N39,N40=@N40,N41=@N41,N42=@N42,N43=@N43,N44=@N44,N45=@N45,N46=@N46,N47=@N47,N48=@N48,N49=@N49
GO

阅读全文
0 0

相关文章推荐

img
取 消
img