我目前写过的最变态存储过程

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

 

CREATE  PROCEDURE [dbo].[P_EveryMeterSTAT]
(@startIndex int,@endIndex int,@datetimes nvarchar(4))
AS
DECLARE @Months int,@Qcum int,@User_Name nvarchar(64),@User_ID nvarchar(50),@WMeter_ID nvarchar(50),@YearTotal int,@OneMeter int

BEGIN
select @YearTotal=sum(MonQcum) from MON_REPORT where MonQcumValid='true' and MonTime like '%'+@datetimes+'%'
create table #tmp_mon(UserID nvarchar(50),UserName nvarchar(64),WMeterID nvarchar(50),TotalYear int,Precent float,mon1 int,mon2 int,mon3 int,mon4 int,mon5 int,mon6 int,mon7 int,mon8 int,mon9 int,mon10 int,mon11 int,mon12 int);
DECLARE MON_USER CURSOR FOR
with #tmp_monuse as (SELECT ROW_NUMBER() OVER (ORDER BY O.WMeterID asc)AS Row, O.WMeterID,O.UserID,E.UserName from T_WMETER O left outer join T_USER E on O.UserID=E.UserID )
SELECT WMeterID,UserID,UserName FROM #tmp_monuse where Row between @startIndex and @endIndex
OPEN MON_USER
FETCH NEXT FROM MON_USER INTO @WMeter_ID,@User_ID,@User_Name
WHILE @@FETCH_STATUS = 0
BEGIN
insert into #tmp_mon(UserID,UserName,WMeterID) values(@User_ID,@User_Name,@WMeter_ID)

select @OneMeter=sum(MonQcum) from MON_REPORT where WMeterID=@WMeter_ID and MonQcumValid='true' and MonTime like '%'+@datetimes+'%'
DECLARE MON_QCUM CURSOR FOR
select month(MonTime) as Mon,MonQcum from MON_REPORT where MonQcumValid='true' and MonTime like '%'+@datetimes+'%' and WMeterID=@WMeter_ID
OPEN MON_QCUM
FETCH NEXT FROM MON_QCUM INTO @Months,@Qcum
WHILE @@FETCH_STATUS = 0
BEGIN
if(@Months=1) update #tmp_mon set mon1 = @Qcum where WMeterID=@WMeter_ID;
if(@Months=2) update #tmp_mon set mon2 = @Qcum where WMeterID=@WMeter_ID;
if(@Months=3) update #tmp_mon set mon3 = @Qcum where WMeterID=@WMeter_ID;
if(@Months=4) update #tmp_mon set mon4 = @Qcum where WMeterID=@WMeter_ID;
if(@Months=5) update #tmp_mon set mon5 = @Qcum where WMeterID=@WMeter_ID;
if(@Months=6) update #tmp_mon set mon6 = @Qcum where WMeterID=@WMeter_ID;
if(@Months=7) update #tmp_mon set mon7 = @Qcum where WMeterID=@WMeter_ID;
if(@Months=8) update #tmp_mon set mon8 = @Qcum where WMeterID=@WMeter_ID;
if(@Months=9) update #tmp_mon set mon9 = @Qcum where WMeterID=@WMeter_ID;
if(@Months=10) update #tmp_mon set mon10 = @Qcum where WMeterID=@WMeter_ID;
if(@Months=11) update #tmp_mon set mon11 = @Qcum where WMeterID=@WMeter_ID;
if(@Months=12) update #tmp_mon set mon12 = @Qcum where WMeterID=@WMeter_ID;
FETCH NEXT FROM MON_QCUM INTO @Months,@Qcum
END
CLOSE MON_QCUM
DEALLOCATE MON_QCUM
update #tmp_mon set TotalYear = @OneMeter where WMeterID=@WMeter_ID;
update #tmp_mon set Precent = Convert(float,@OneMeter)/@YearTotal where WMeterID=@WMeter_ID;
FETCH NEXT FROM MON_USER INTO @WMeter_ID,@User_ID,@User_Name
END
CLOSE MON_USER
DEALLOCATE MON_USER
select * from #tmp_mon
END