Question

sqlserver 2个返回值参数的存储过程调用方法？

ALTER PROCEDURE [dbo].[proc_res_get_check_result] (
@i_olt_ip VARCHAR
)
AS
DECLARE @iCount int

BEGIN

if @i_olt_ip = 'all'
select * from
(
select ro.onu as onu,ro.olt_ip as olt_ip,ro.pon as pon,ro.onu_id as onuid,ro.check_time as check_time,ro.flag as flag,
ro.onu_name as res_name,ro.onu_sn as res_sn,po.onu_dev_name as ems_name,po.loid as ems_sn
from res_onu_data ro
left join pm_onu po on po.onu_name = ro.onu
where ro.olt_ip in (select t.olt_ip from res_check_task t where t.flag = 2)
union
select re.onu as onu,re.olt_ip as olt_ip,re.pon as pon,re.onu_id as onuid,re.check_time as check_time,'20000000000000000000' as flag,
'' as res_name,'' as res_sn,po.onu_dev_name as ems_name,po.loid as ems_sn
from res_check_ems re,pm_onu po
where po.onu_name = re.onu and re.olt_ip in (select t.olt_ip from res_check_task t where t.flag = 2)
)rr order by rr.onu;
else
begin
select @iCount=count(*) from pm_olt pOlt where pOlt.Olt_Ip = @i_olt_ip;
if @iCount = 0
return -5001

select @iCount=count(*) from res_check_task t where t.olt_ip = @i_olt_ip and t.flag = 2;

if @iCount = 0
return -5001

select * from
(
select ro.onu as onu,ro.olt_ip as olt_ip,ro.pon as pon,ro.onu_id as onuid,ro.check_time as check_time,ro.flag as flag,
ro.onu_name as res_name,ro.onu_sn as res_sn,po.onu_dev_name as ems_name,po.loid as ems_sn
from res_onu_data ro
left join pm_onu po on po.onu_name = ro.onu
where ro.olt_ip = @i_olt_ip
union
select re.onu as onu,re.olt_ip as olt_ip,re.pon as pon,re.onu_id as onuid,re.check_time as check_time,'20000000000000000000' as flag,
'' as res_name,'' as res_sn,po.onu_dev_name as ems_name,po.loid as ems_sn
from res_check_ems re,pm_onu po
where po.onu_name = re.onu and re.olt_ip = @i_olt_ip
)rr order by rr.onu;
end

if @@error <> 0
return -9000

return 0

END

在WEB 的jdbc下调用一个2个返回值的存储过程 返回值一个是int型的 一个是游标形式的数组集合 有没有会调用的 请大神回答

Answer 1

你这个是oracle的，sqlserver不是这样实现的。
返回值int 你要这样定义
ALTER PROCEDURE [dbo].[proc_res_get_check_result] (
@i_olt_ip VARCHAR output
)
AS
然后结果数据集直接在存储过程中
select * from
(
select ro.onu as onu,ro.olt_ip as olt_ip,ro.pon as pon,ro.onu_id as onuid,ro.check_time as check_time,ro.flag as flag,
ro.onu_name as res_name,ro.onu_sn as res_sn,po.onu_dev_name as ems_name,po.loid as ems_sn
from res_onu_data ro
left join pm_onu po on po.onu_name = ro.onu
where ro.olt_ip = @i_olt_ip
union
select re.onu as onu,re.olt_ip as olt_ip,re.pon as pon,re.onu_id as onuid,re.check_time as check_time,'20000000000000000000' as flag,
'' as res_name,'' as res_sn,po.onu_dev_name as ems_name,po.loid as ems_sn
from res_check_ems re,pm_onu po
where po.onu_name = re.onu and re.olt_ip = @i_olt_ip
)rr order by rr.onu;

就可以了

Answer 2

你为什么不全部用数据集呢？将你返回的值做成数据集这样就不用返回值了，不然你要做两次判断这样你的速度会更加慢！