有一个存储过程：
ALTER procedure [dbo].[mapajax]
(
@i_op_id varchar(32) -- 操作员标识
,@i_enterprise_id varchar(32) -- 企业ID
,@o_score varchar(50) out -- 动态评分
,@o_level varchar(1) out -- 动态评级
,@o_outcode int out -- 输出代码
,@o_outmsg varchar(500) out -- 输出信息
)
as
begin
set nocount on;
-- =========================== 声明变量 ===========================
-- 自定义变量
declare @zdjs_num numeric(12,2); -- 制度建设
declare @yhzl_num numeric(12,2); -- 隐患治理
declare @sgqk_num numeric(12,2); -- 事故情况
declare @rcgl_num numeric(12,2); -- 日常管理
declare @bzh_num numeric(12,2); -- 标准化
declare @rycz_num numeric(12,2); -- 人员持证情况
declare @hdjl_num numeric(12,2); -- 活动记录
declare @v_year varchar(4);
declare @v_quarter varchar(4);
-- =========================== 声明游标 ===========================
-- =========================== 声明临时表==========================
-- 调试临时表
create table #tmp_debug (
debug_id int
, debug_time varchar(50) -- 调试时间
, debug_info varchar(2000) -- 调试信息
);
-- =========================== 输入检查 ===========================
set @o_outcode=0;
set @o_outmsg='';
if @i_op_id is null or @i_op_id=''
begin
set @o_outcode =-1;
set @o_outmsg =@o_outmsg+'【操作员标识】不能为空。';
end
if @o_outcode<>0
begin
set @o_outmsg='输入参数错误：'+@o_outmsg;
return;
end
-- =========================== 数据预处理 ===========================
set @v_year = datename(YEAR, DATEADD(q,DATEDIFF(q,0,GETDATE())-1,0)); -- 上个季度的年份
set @v_quarter = datename(q,DATEADD(q,DATEDIFF(q,0,GETDATE())-1,0)); -- 上个季度
-- 制度建设
sel ect
@zdjs_num = SUM(t.REAL_SCORE)/2
from P_PER_EVA_RESULT_INFO_jx t
where 1=1
and t.ENTERPRISE_ID = @i_enterprise_id
and t.EVA_YEAR = @v_year
and t.EVA_QUARTER = @v_quarter
and t.EVA_ID in ('EVA00000000000000000000000000015','EVA00000000000000000000000000001');
if @zdjs_num is null
set @zdjs_num = 0;
-- 隐患治理
sel ect
@yhzl_num = SUM(t.REAL_SCORE)/7
from P_PER_EVA_RESULT_INFO_jx t
where 1=1
and t.ENTERPRISE_ID = @i_enterprise_id
and t.EVA_YEAR = @v_year
and t.EVA_QUARTER = @v_quarter
and t.EVA_ID in ('EVA00000000000000000000000000002','EVA00000000000000000000000000003','EVA00000000000000000000000000004','EVA00000000000000000000000000005','EVA00000000000000000000000000006','EVA00000000000000000000000000007');
if @yhzl_num is null
set @yhzl_num = 0;
-- 事故情况
sel ect
@sgqk_num = SUM(t.REAL_SCORE)
from P_PER_EVA_RESULT_INFO_jx t
where 1=1
and t.ENTERPRISE_ID = @i_enterprise_id
and t.EVA_YEAR = @v_year
and t.EVA_QUARTER = @v_quarter
and t.EVA_ID = 'EVA00000000000000000000000000010';
if @sgqk_num is null
set @sgqk_num = 0;
-- 日常管理
sel ect
@rcgl_num = SUM(t.REAL_SCORE)/4
from P_PER_EVA_RESULT_INFO_jx t
where 1=1
and t.ENTERPRISE_ID = @i_enterprise_id
and t.EVA_YEAR = @v_year
and t.EVA_QUARTER = @v_quarter
and t.EVA_ID in ('EVA00000000000000000000000000011','EVA00000000000000000000000000012','EVA00000000000000000000000000015','EVA00000000000000000000000000017');
if @rcgl_num is null
set @rcgl_num = 0;
-- 标准化
sel ect
@bzh_num = SUM(t.REAL_SCORE)/2
from P_PER_EVA_RESULT_INFO_jx t
where 1=1
and t.ENTERPRISE_ID = @i_enterprise_id
and t.EVA_YEAR = @v_year
and t.EVA_QUARTER = @v_quarter
and t.EVA_ID in ('EVA00000000000000000000000000008','EVA00000000000000000000000000009');
if @bzh_num is null
set @bzh_num = 0;
-- 人员持证情况
sel ect
@rycz_num = SUM(t.REAL_SCORE)
from P_PER_EVA_RESULT_INFO_jx t
where 1=1
and t.ENTERPRISE_ID = @i_enterprise_id
and t.EVA_YEAR = @v_year
and t.EVA_QUARTER = @v_quarter
and t.EVA_ID = 'EVA00000000000000000000000000016';
if @rycz_num is null
set @rycz_num = 0;
--活动记录
sel ect
@hdjl_num = SUM(t.REAL_SCORE)/2
from P_PER_EVA_RESULT_INFO_jx t
where 1=1
and t.ENTERPRISE_ID = @i_enterprise_id
and t.EVA_YEAR = @v_year
and t.EVA_QUARTER = @v_quarter
--and t.EVA_ID in ('EVA00000000000000000000000000013','EVA00000000000000000000000000014', 'EVA00000000000000000000000000017');
and t.EVA_ID in ('EVA00000000000000000000000000013','EVA00000000000000000000000000014');
if @hdjl_num is null
set @hdjl_num = 0;
-- =========================== 业务处理 ===========================
begin try
begin tran
-- todo begin
sel ect
@o_score = t.FINAL_SCORE
,@o_level = t.EVA_GRADE
from P_PER_EVA_RESULT_INFO_jx t
where 1=1
and t.IS_ACTIVE = '1'
and t.ITEM_ID = 'eva_grade_item_id000000000000000'
and t.EVA_ID = 'eva_grade_eva_id0000000000000000'
and t.EVA_YEAR = @v_year
and t.EVA_QUARTER = @v_quarter
and t.ENTERPRISE_ID = @i_enterprise_id;
-- todo end
commit tran;
-- =========================== 结果集返回 ===========================
sel ect
@zdjs_num as zdjs_num -- 制度建设
, @yhzl_num as yhzl_num -- 隐患治理
, @sgqk_num as sgqk_num -- 事故情况
, @rcgl_num as rcgl_num -- 日常管理
, @bzh_num as bzh_num -- 标准化
, @rycz_num as rycz_num -- 人员持证情况
, @hdjl_num as hdjl_num -- 活动记录
;
end try
-- =========================== 声明异常 ===========================
begin catch
end catch--------结束异常处理
end
Dim entid As String="2DB0F74160C94EBD8CD7BB3F186ED4D1"
Dim cmd As new SQL Command
cmd.C
cmd.CommandText = "mapajax"
cmd.StoredProcedure = True
cmd.Parameters.Add("@yhzl_num",0, False) '返回参数
cmd.Parameters.Add("@sgqk_num",0 , False) '返回参数
cmd.Parameters.Add("@rcgl_num",0 , False) '返回参数
cmd.Parameters.Add("@bzh_num",0 , False) '返回参数
cmd.Parameters.Add("@rycz_num",0 , False) '返回参数
cmd.Parameters.Add("@hdjl_num", 0, False) '返回参数
cmd.Parameters.Add("@i_op_id","radar") '输入参数
cmd.Parameters.Add("@i_enterprise_id",entid) '输入参数
cmd.Parameters.Add("@o_score", "", True) '输出参数
cmd.Parameters.Add("@o_level", "", True) '输出参数
cmd.ExecuteNonQuery
Output.Show( cmd.Parameters("@o_score"))
Output.Show( cmd.Parameters("@o_level"))
Output.Show( cmd.Parameters("@sgqk_num"))
加好友 
发短信
Post By：2018/9/1 14:55:00 [只看该作者]
