Sql Server 中带参数的存储过程及在Reporting Services 中的调用方法

 

将处理好的数据放入到数据库的表中，在报表中就可以根据条件来检索了

简单记于此， 以备以后查找

Create  proc [dbo].[sp_ReportDate]
@date1 datetime , @date2 datetime , @li_num int , @li_target int

as
begin
  declare @var_retrun varchar(10)
  declare @li_train_num int
  declare @li_hours     int
  declare @li_fact      int
  declare @li_pres      decimal(6,2)
  declare @li_content   int
  declare @li_content2  decimal(5,2)
  declare @li_fee       decimal(10,2)
  declare @li_nei       decimal(10,2)
  declare @li_te        decimal(10,2)
  declare @li_wai       decimal(10,2)
  declare @li_count     int
  set @var_retrun='successful'

  set @li_train_num = 0
  set @li_hours = 0
  set @li_fact = 0
  set @li_pres = 0
  set @li_content = 0
  set @li_content2 = 0
  set @li_fee = 0
  set @li_nei = 0
  set @li_te = 0
  set @li_wai = 0
  set @li_count = 0

  begin
    delete from train_report_date where train_beg >=@date1 and train_end <=@date2
 
  end

  begin
     --参加培训人次
     select @li_train_num =count(user_id) from form_train_linem, form_train_lined
     where form_train_linem.taskid = form_train_lined.taskid
       and form_train_linem.train_beg >=@date1
       and form_train_linem.train_end <=@date2
     --培训总时数  
     select @li_hours = sum(isnull(train_hours,0)) from form_train_linem
      where form_train_linem.train_beg >=@date1
        and form_train_linem.train_end <=@date2
     --培训总费用
     select @li_fee =sum(isnull(train_fee,0)) from form_train_linem
     where form_train_linem.train_beg >=@date1
       and form_train_linem.train_end <=@date2
     --内部讲师费
     select @li_nei =sum(isnull(train_fee,0)) from form_train_linem
     where form_train_linem.train_beg >=@date1
       and form_train_linem.train_end <=@date2
       and source_fee ='内部讲师费'    
     --特种作业费用
     select @li_te =sum(isnull(train_fee,0)) from form_train_linem
     where form_train_linem.train_beg >=@date1
       and form_train_linem.train_end <=@date2
       and source_fee ='特种作业费'
    --外训费用
    select @li_wai =sum(isnull(train_fee,0)) from form_train_linem
     where form_train_linem.train_beg >=@date1
       and form_train_linem.train_end <=@date2
       and source_fee ='外训费用'

  end
  --实际人均时数
  if @li_target <>0
     set @li_fact = @li_hours/@li_num
     set @li_pres = Round(@li_fact/@li_target,2)

  begin
     select @li_content = sum(isnull(content,0)) from form_train_linem
     where train_beg >=@date1
       and train_end <=@date2
     select @li_count = count(*) from form_train_linem
     where train_beg >=@date1
       and train_end <=@date2

  end
 
  if @li_count <> 0
     set @li_content2 = Round(@li_content/@li_count,2)
 
  INSERT INTO train_report_date(train_beg, train_end, person_num,train_num,    train_hours,fact_num, target_num,train_pres,train_content,train_fee,  train_nei,train_te,train_wai)
                        VALUES (@date1,    @date2,    @li_num,   @li_train_num,@li_hours,  @li_fact, @li_target,@li_pres  ,@li_content2   ,@li_fee,   @li_nei,   @li_te, @li_wai  )

end



调用的该存储过程的语句


EXEC sp_ReportDate @date1 = @date1, @date2 = @date2, @li_num = @li_num,
@li_target = @li_target SELECT *
                         FROM train_report_date
                         WHERE train_beg >= @date1 AND train_end <= @date2



在网上找的资料只能做为参考， 最后要自己实践才能得出正确的结论。