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

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