流浪的风__ 2016-08-09 06:34 采纳率: 0%
浏览 2626
已结题

jdbc执行SqlServer存储过程时 成功后返回结果集,执行失败后 return值的问题

最近遇到一个存储过程,由于接口不是很规范,由于执行成功和执行失败返回的结果不一致,求大神帮忙解答,只有这点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???????????在线等,求助!!!!!!!!!!!!!!
  • 写回答

4条回答 默认 最新

  • 流浪的风__ 2016-08-09 06:40
    关注

    有没有人啊。。。。。求帮忙

    评论

报告相同问题?

悬赏问题

  • ¥15 lammps Gpu加速出错
  • ¥15 关于PLUS模型中kapaa值的问题
  • ¥15 关于博途V17进行仿真时无法建立连接问题
  • ¥15 请问下这个红框里面是什么文档或者记事本编辑器
  • ¥15 机器学习教材中的例题询问
  • ¥15 求.net core 几款免费的pdf编辑器
  • ¥15 为什么安装HCL 和virtualbox之后没有找到VirtualBoxHost-OnlyNetWork?
  • ¥15 C# P/Invoke的效率问题
  • ¥20 thinkphp适配人大金仓问题
  • ¥20 Oracle替换.dbf文件后无法连接,如何解决?(相关搜索:数据库|死循环)