Retrieve the returned value from sql server stored procedure using java

Question

Here is my store procedure that returns a value. I need to call this procedure and get that return value into my java program.



CREATE PROCEDURE my_procedure @advisor de , @adv_xml xml   

AS      


begin        
  declare 
    @psrg_idi idi,
    @adv_cd cd,
    @CurrDate cdt

  set @adv_cd = (select adv_cd from dbo.ADVICE_LK where upper(rtrim(adv_de)) = upper(@advisor))
  set @psrg_idi = 0    
  set @CurrDate = getdate()

    BEGIN TRY     
      exec my_proc_2 @CurrDate,@psrg_idi output

      insert into
          ADVICE
          (psrg_idi,
           adv_cd,
           psra_original_xml)  
      values
        (@psrg_idi,
         @adv_cd,
         @adv_xml)  

      select 
        @psrg_idi as psrg_idi

    END TRY     
    BEGIN CATCH    
       DECLARE @ErrorMessage NVARCHAR(4000);    
       DECLARE @ErrorSeverity INT;    
       DECLARE @ErrorState INT;     
       SELECT     
         @ErrorMessage = ERROR_MESSAGE(),    
    @ErrorSeverity = ERROR_SEVERITY(),    
    @ErrorState = ERROR_STATE();    
   -- Use RAISERROR inside the CATCH block to return error    
   -- information about the original error that caused    
   -- execution to jump to the CATCH block.    
   RAISERROR (@ErrorMessage, -- Message text.    
     @ErrorSeverity, -- Severity.    
     @ErrorState -- State.    
     );    
   return -1    
     END CATCH    

end

Here is how I am trying to get this value into my java program returned by the above stored procedure. When I call this procedure from java, all the expected values are stored into database tables. But I am receiving the returned value groupId as '0'.. any idea or help will be highly appreciated.



CallableStatement cs = con.prepareCall("{? = call my_procedure (?,?)}");                
int i = 0;
cs.registerOutParameter(++i, java.sql.Types.INTEGER);
cs.setString(++i, advisor);
cs.setString(++i, adviceXml);

cs.execute();
int groupId = cs.getInt(1);

I have already reviewed the accepted answer https://stackoverflow.com/a/1948518/674476 . I am also trying in the same way, but somehow not able to get returned value

@ dasblinkenlight, MS SQL Server

Muhammad Ijaz
– Muhammad Ijaz

2013-11-28 14:13:55 +00:00
Commented Nov 28, 2013 at 14:13 — Muhammad Ijaz
– Muhammad Ijaz, Commented Nov 28, 2013 at 14:13

Community · Accepted Answer · 2017-05-23 12:02:36Z

6

See accepted answer here.

First, you should decide if your SP will return a ResultSet or not. I mean, when you catch an error, you do RETURN -1. But, if all works fine, you perform a SELECT, which returns a ResultSet.

Said that, if you choose to return a value, you have to do:

CallableStatement cs = con.prepareCall("{? = call my_procedure (?,?)}");                
int i = 0;
cs.registerOutParameter(++i, java.sql.Types.INTEGER);
cs.setString(++i, advisor);
cs.setString(++i, adviceXml);

cs.execute();
int groupId = cs.getInt(1);

But, if you return a ResultSet, you have to do:

CallableStatement cs = con.prepareCall("{call my_procedure (?,?)}");                
int i = 0;
cs.setString(++i, advisor);
cs.setString(++i, adviceXml);

ResultSet rs = cs.executeQuery();
if (rs.next())
    int groupId = rs.getInt(1);

I've tested both methods using a toy SP:

CREATE PROCEDURE my_procedure @v1 int, @v2 int 
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

    --SELECT @v1 + @v2 as sumVal -- Comment this and uncomment previous line to test Return
RETURN @v1+@v2 -- Comment this and uncomment previous line to test ResultSet
END

edited May 23, 2017 at 12:02

CommunityBot

11 silver badge

answered Nov 28, 2013 at 14:20

neutrino

2,3174 gold badges20 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Imran Over a year ago

Question is that how execute function return values ??

Muhammad Ijaz Over a year ago

@neutrino, this is of no use... I am trying the same way as of the accepted answer stackoverflow.com/a/1948518/674476

neutrino Over a year ago

But, in your Stored procedure, you only do a return -1 when catching errors, you don't return anything if all works fine.

neutrino Over a year ago

@Imran Where have I said that cs.execute(); should return a value? Is that the reason for the down vote? What I say is that you should call cs.execute(); not cs.executeUpdate(); when calling to the SP.

Muhammad Ijaz Over a year ago

this "select @psrg_idi as psrg_idi" statement is returning the value. In case of any exception, it will return -1

|

Muhammad Ijaz · Accepted Answer · 2013-12-04 08:53:37Z

Below is the solution to the problem and it works perfectly fine in my case.

CallableStatement cs = con.prepareCall("{call my_procedure(?,?)}");  
int i = 0;
cs.setString(++i, advisor);
cs.setString(++i, adviceXml);

boolean isRs = cs.execute();
int updateCount = cs.getUpdateCount();
// cs.getUpdateCount() will return -1 if the current result is a ResultSet object 
// or there are no more results
// cs.getMoreResults() will return true  if the next result is a ResultSet object; 
// false if it is an update count or there are no more results

while (!isRs && (cs.getUpdateCount() != -1)) {
    isRs = cs.getMoreResults(); 
}

if (isRs) {
    ResultSet rs = cs.getResultSet();
    try {
        if (rs.next()) {
             groupId = rs.getString("psrg_idi");
        }
    } finally {
        rs.close();
    }
}

Collectives™ on Stack Overflow

Retrieve the returned value from sql server stored procedure using java

2 Answers 2

10 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related