利用exec方法执行存储过程示例

最新推荐文章于 2025-05-21 21:29:46 发布

侠仪

最新推荐文章于 2025-05-21 21:29:46 发布

阅读量1.7w

点赞数 6

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/lxl743p/article/details/44018535

Sql数据库同时被 2 个专栏收录

17 篇文章

订阅专栏

典型存储过程示例

2 篇文章

订阅专栏

此博客介绍了一个用于数据库的存储过程，该过程能够针对特定建筑ID，在给定的时间段内，按月汇总各类能耗数据，并进行相应的数据清理与统计。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

CREATE PROCEDURE [dbo].[proc_AmountEnergy_Month_SingleBuild]
   -- Add the parameters for the stored procedure here
    @P_BuildID varchar(20)='100000A007',
   @P_Date varChar(20)='2008-01-01',
   @P_LastPeriodNum int=1 --数据持续时段数目
AS
BEGIN

--分类分项代码指针
DECLARE EnergyItemCodeCur CURSOR FOR select F_EnergyItemCode from T_DT_EnergyItemDict where F_EnergyItemType_Name ='分项能耗' and F_EnergyItemState=1

--Declare @BuildID varchar(16)
Declare @EnergyItemCode varchar(16)
Declare @dbValue Decimal(18,2)
Declare @StartT varchar(20)
Declare @EndT varchar(20)
Declare @i int
DECLARE @Sql varchar(2000)

set @Sql=''
--
set @i=0;
set @StartT=left(@P_Date,7) + '-01 00:00:00';
while @i<@P_LastPeriodNum
begin
    set @EndT=convert(varchar(20),dateadd(day,-1,dateadd(month,1,@StartT)),120)
   OPEN EnergyItemCodeCur
   FETCH NEXT FROM EnergyItemCodeCur Into @EnergyItemCode
   WHILE @@FETCH_STATUS = 0
       begin
       --先清理记录
          set @Sql='delete from T_EC_EnergyItemMonthResult where F_BuildID ='''+@P_BuildID +''' and F_EnergyItemCode='''+@EnergyItemCode +''' and F_StartMonth ='''+@StartT+''' and F_EndMonth ='''+@EndT +''''
          print '语句执行:' + @sql
         exec(@sql);
           --将指定建筑ID、能耗分项的日数据统计出来
           --insert into T_EC_EnergyItemMonthResult(F_MonthResultID,F_BuildID,F_EnergyItemCode,F_StartMonth,F_EndMonth,F_MonthValue,F_MonthEquValue,F_DayMaxValue,F_DayMaxEquValue,F_DayMinValue,F_DayMinEquValue,F_State)
           --           select '',@P_BuildID,@EnergyItemCode,@StartT,@EndT,sum(Day_Energy),sum(Day_Energy)*0.1229,max(Day_Energy) ,max(Day_Energy)*0.1229,min(Day_Energy) ,min(Day_Energy) *0.1229,1
           --           from (SELECT DISTINCT     F_BuildID ,    F_EnergyItemCode,
           --               CAST(YEAR(F_StartDay) AS varchar(4)) + '年' + DATENAME(mm, F_StartDay) + '月' AS 年月,
           --               CAST(F_DayValue AS decimal(18, 2)) AS Day_Energy FROM T_EC_EnergyItemDayResult
               --           where F_StartDay >=@StartT and F_EndDay<= @EndT and F_BuildID=@P_BuildID and F_EnergyItemCode = @EnergyItemCode ) a group by F_BuildID,F_EnergyItemCode,年月

          set @Sql =' insert into T_EC_EnergyItemMonthResult(F_MonthResultID,F_BuildID,F_EnergyItemCode,F_StartMonth,F_EndMonth,F_MonthValue,F_MonthEquValue,F_DayMaxValue,F_DayMaxEquValue,F_DayMinValue,F_DayMinEquValue,F_State) '
          set @Sql=@Sql+' select '''','''+@P_BuildID+''','''+@EnergyItemCode+''','''+@StartT+''','''+@EndT+''',sum(Day_Energy),sum(Day_Energy)*0.1229,max(Day_Energy) ,max(Day_Energy)*0.1229,min(Day_Energy) ,min(Day_Energy) *0.1229,1 '
          set @Sql=@Sql+' from (SELECT DISTINCT     F_BuildID ,    F_EnergyItemCode,
                           CAST(YEAR(F_StartDay) AS varchar(4)) + ''年'' + DATENAME(mm, F_StartDay) + ''月'' + DATENAME(dd, F_StartDay) + ''日'' AS 年月日, '
          set @Sql=@Sql+' CAST(F_DayValue AS decimal(18, 2)) AS Day_Energy FROM T_EC_EnergyItemDayResult '
           set @Sql=@Sql+ '               where F_StartDay >='''+@StartT+''' and F_EndDay<= '''+@EndT+''' and F_BuildID='''+@P_BuildID+''' and F_EnergyItemCode = '''+@EnergyItemCode+''' ) a group by F_BuildID,F_EnergyItemCode,年月日'
           exec (@sql)
           print @sql
           if @@ERROR <> 0
           begin
              print '语句执行错误<err='+cast(@@ERROR as varchar(10)) +'>:
                    ' + @sql

           end

           FETCH NEXT FROM EnergyItemCodeCur Into @EnergyItemCode
       end
   CLOSE EnergyItemCodeCur
   set @i=@i+1
end
DEALLOCATE EnergyItemCodeCur
END

GO