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

在網上找的資料只能做為參考， 最後要自己實踐才能得出正確的結論。