Stored Procedure with parameters/variables - how to get result from procedure execution?

Question

Have a question about stored procedure:
How to retrieve the variable's value from stored procedure that is declared as an output variable. Running procedure as :

EXEC pn_investment_type_eqt {args}

Here is stored procedure:

create proc pn_investment_type_eqt
(
 @product_type varchar(10),
 @country varchar(10),
 @fi_treatment varchar(1)= '',
 @investment_type varchar(10) output,
 @investment_description varchar(50) output
)
as
 set nocount on

if @country <> 'US'
   select @country = 'FOREIGN'

if ( @fi_treatment not in ('Y','N') )
   select @fi_treatment = 'N'

if not exists(select 1 from d_investment_type_eqt
              where product_type = @product_type and isNull(country,'') = isNull(@country,'') and fi_treatment = @fi_treatment and row_status='A' )
begin
  select @country = 'ANY'
end

if exists ( select 1 from d_investment_type_eqt
            where product_type = @product_type and isNull(country,'') = isNull(@country,'') and fi_treatment = @fi_treatment and row_status='A' )
begin
   select @investment_type= investment_type , @investment_description = description
   from d_investment_type_eqt
   where product_type = @product_type and isNull(country,'') = isNull(@country,'') and fi_treatment = @fi_treatment and row_status='A'

end
 else
   return (-1)

I need to get values of @investment_type & @investment_description.

I can't change procedure.

I am using a custom implementation of jdbcTemplate of Spring 2.0 (sql, mapper, args)

DB is Sybase

How could I get results from this stored procedure ?

"if @country <> 'US' select @country = 'FOREIGN'"... this works on *so* many levels ! :o) — Black Light, Jun 20 '11 at 14:13

score 2 · Accepted Answer · edited May 23 '17 at 10:08

2

Take a look at this sybase-in-and-out-parameters post, it possibly helps you further.

edited May 23 '17 at 10:08

Community

1
1

answered Jun 19 '11 at 14:42

Omnaest

3,096
1
19
18

Thank you, I will try it. (CallableStatement) – kislo_metal Jun 19 '11 at 15:14

score 0 · Answer 2 · answered Jun 19 '11 at 15:08

I decided to use next design :

declare @investment_type_value varchar(10)
declare @investment_description_value varchar(50)
SET @investment_type_value = '3041'

EXEC global..pn_investment_type_eqt 'CVPFDST', 'US', 'N', @investment_type = @investment_type_value output , @investment_description = @investment_description_value OUTPUT
select  investment_type = @investment_type_value, investment_description = @investment_description_value
GO

It give me possibility to retrieve variable values.

Stored Procedure with parameters/variables - how to get result from procedure execution?

2 Answers2