Stored Procedures with parameters in SQL Server and calling methods in reporting services

 

Put the processed data in the database table, and you can search the data in the report based on the conditions.

Simply remember this for Future Search

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 ()
declare @ li_1_decimal ()
declare @ li_nei decimal)
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_defaults = 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
-- Participants
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
-- Total training hours
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
-- Total training fee
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
-- Internal instructor fee
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_ = 'internal instructor payby'
-- Special operation 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 = 'special operation payby'
-- External training 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 = 'external training cost'

End
-- Actual per capita hours
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 inline (train_beg, train_end, person_num, train_num, inline, fact_num, target_num, train_pres, train_content, train_inline, train_nei, train_te, train_wai)
Values (@ date1, @ date2, @ li_num, @ override, @ li_hours, @ li_fact, @ li_target, @ li_pres, @ li_content2, @ li_target, @ li_nei, @ li_te, @ li_wai)

End

Statement of the stored procedure called

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

The materials found on the Internet can only be used as a reference, and the correct conclusion can be drawn only after you practice them.