最近遇到一个存储过程,由于接口不是很规范,由于执行成功和执行失败返回的结果不一致,求大神帮忙解答,只有这点c币了,全部奉上,贴代码;
CREATE PROCEDURE [dbo].[proc_phone_gh]
(
@p_id char(12), --patient_id 患者ID
@unit_sn varchar(7), --office_id 挂号科室编号
@request_date varchar(10), --czdate 挂号日期
@ampm char(1), --ampm 上/下午
@clinic_type varchar(2), --card_code 号源种类
@doctor_sn varchar(5), --doctor_id 医生编号,普通号可为空
@sequence smallint, --gh_sequence 就诊顺序号,手机app自行计算
@app_order varchar(30), --app_order 预约码
@app_type char(1) --cretificate_type 方式:1手机APP/2微信
)
AS
declare @msg varchar(50),
@record_sn int,
@group_sn varchar(7),
@mz_dept_no varchar(2),
@opera varchar(5),
@times int,
@req_type varchar(2) ,
@gh_sequence smallint ,
@ledger_sn varchar(20)
set @group_sn=''
set @req_type='15' --挂号途径 13 手机app
set @mz_dept_no='1' --是否是分院
begin transaction
update mz_patient_mi set max_times= max_times+1
where patient_id=@p_id
if @@error<>0
begin
set @msg = '更新mz_patient_mi表失败'
select @msg return_msg
rollback transaction
return 0
end
set @times=(select max_times from mz_patient_mi where patient_id=@p_id)
set @ledger_sn =(select max_ledger_sn from mz_patient_mi where patient_id=@p_id)
begin
select top 1 @record_sn =record_sn ,@gh_sequence=gh_sequence
from gh_schedule
where 1=1
and request_date=@request_date and ampm=@ampm
and unit_sn=@unit_sn
and isnull(doctor_sn,'') = @doctor_sn
and clinic_type like @clinic_type
and charindex('15',visit_req_type)>0 --挂号方式
and status_flag='0'
order by abs(gh_sequence)
insert into gh_appointment_external
(app_order,name,sex,social_no,phone,gh_sequence,reg_time,request_date,visit_dept,doctor_code,ampm,clinic_type,req_type
,status,patient_id,times,phone2, source_type,import_date,import_opera,gh_record_sn,certificate_type)
select @app_order,name,sex,social_no,home_tel as phone,@gh_sequence as gh_sequence,convert(varchar(20),getdate(),120) as
reg_time,@request_date
,@unit_sn,case when isnull(@doctor_sn,'')='' then '-1' else @doctor_sn end as doctor_code,@ampm,@clinic_type,@req_type as
req_type,'0' as status
,@p_id,@times,'00000000','手机App预约挂号' as source_type,convert(varchar(20),getdate(),120) as import_date,'' as
import_opera,@record_sn as gh_record_sn,case when @app_type='1' then '2' when @app_type='2' then '3' end
from view_mz_patient_mi
where patient_id=@p_id
if @@error<>0 or @@ROWCOUNT<1
begin
set @msg='插入预约登记表gh_appointment_register失败!'
select @msg return_msg
rollback transaction
return 0
end
update gh_schedule set status_flag='1',req_type=@req_type
where record_sn=@record_sn and gh_sequence=@gh_sequence
and request_date=CONVERT(varchar(20), @request_date, 120 )
and unit_sn=@unit_sn
and isnull(group_sn,'')=@group_sn
and clinic_type =@clinic_type
and isnull(ampm,'')=@ampm
and charindex(@req_type,visit_req_type)>0
and mz_dept_no=@mz_dept_no
if @@error<>0 or @@ROWCOUNT<1
begin
set @msg = '更新gh_schedule表失败'
select @msg return_msg
rollback transaction
return 0
end
commit transaction
end
select isnull((select name from gh_zd_appointment_place where code =convert(varchar(7),@unit_sn)),(select comment from zd_unit_code
where code =convert(varchar(7),@unit_sn))) as address,substring(convert(varchar,@request_date,120),1,11) +' '+ convert(varchar
(20),dz_time1)+'至'+convert(varchar(20),dz_time2) as mtime ,
@gh_sequence xh ,@times times,@ledger_sn checkOuts
from --gh_zd_appointment_place a,
encounter_time h,gh_zd_clinic_type_query b
where b.zj_flag *=h.charge_type
and h.end_no>=@gh_sequence and h.begin_no<=@gh_sequence
and h.ampm=convert(varchar(10),@ampm)
and b.code=convert(char(10),@clinic_type)
1.预约成功时返回最下面的一个结果集
select isnull((select name from gh_zd_appointment_place where code =convert(varchar(7),@unit_sn)),(select comment from zd_unit_code
where code =convert(varchar(7),@unit_sn))) as address,substring(convert(varchar,@request_date,120),1,11) +' '+ convert(varchar
(20),dz_time1)+'至'+convert(varchar(20),dz_time2) as mtime ,
@gh_sequence xh ,@times times,@ledger_sn checkOuts
from --gh_zd_appointment_place a,
encounter_time h,gh_zd_clinic_type_query b
where b.zj_flag *=h.charge_type
and h.end_no>=@gh_sequence and h.begin_no<=@gh_sequence
and h.ampm=convert(varchar(10),@ampm)
and b.code=convert(char(10),@clinic_type)
2.执行失败时,返回return值,和return_msg信息;
3.jdbc代码如下:
String storedProc = "{call proc_phone_gh(?,?,?,?,?,?,?,?,?)}";
CallableStatement cs = con.prepareCall(storedProc);
cs.setString(1, (String)proParams[0]);
cs.setString(2, (String)proParams[1]);
cs.setString(3, (String)proParams[2]);
cs.setString(4, (String)proParams[3]);
cs.setString(5, (String)proParams[4]);
cs.setString(6, (String)proParams[5]);
cs.setInt(7, (Integer)proParams[6]);
cs.setString(8, (String)proParams[7]);
cs.setString(9, (String)proParams[8]);
ResultSet rs = cs.executeQuery();
执行到ResultSet rs = cs.executeQuery();时,报异常:该语句没有结果集,该语句在数据库里可以执行也可以得到结果集。
请问各位大神们,如何获取到成功的结果集 和失败时的return值和return_msg???????????在线等,求助!!!!!!!!!!!!!!