专家坐堂-[求助]执行存储过程这样写对么？Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

有一个存储过程：

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 -- 输出信息

)

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