CODEWITHSUNDEEP
javastored-proceduresjdbcsybase
Sharath N
Sharath N

Reputation: 100

How to handle two stored procedures, one returning a single value and the other returning rows?

I am using a Sybase database.

Suppose if I have a query like ...

CREATE PROCEDURE dbo.procedureA (
     @a int
  )
  as
  begin
     IF EXISTS (SELECT 1 FROM dbo.T WHERE a = @a)
         return 1
     ELSE
         return -1
   end

... and another procedure containing a simple SELECT query returning multiple rows.

I have to handle both of them in a single program. How can I determine at runtime whether the procedure is returning a value or a set of rows?

P.S: I cannot use OUT parameter.

Upvotes: 0

Views: 419

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123559

The .execute() method of the CallableStatement object returns a boolean value indicating whether the (first) result returned is a ResultSet. You can use that value to determine whether the stored procedure returned a ResultSet, and then get the RETURN value (if any), as follows:

try (CallableStatement s = conn.prepareCall("{ ? = CALL dbo.test3 (?) }")) {
    s.registerOutParameter(1, Types.INTEGER);
    s.setInt(2, 3);  // set input parameter to value 3, for example
    if (s.execute()) {
        // .execute() returned true, so we have a ResultSet
        System.out.println("ResultSet values:");
        try (ResultSet rs = s.getResultSet()) {
            while (rs.next()) {
                System.out.println(rs.getInt(1));
            }
        }
        System.out.println("");
    }
    // get RETURN value (or zero if no RETURN statement in stored procedure)
    System.out.printf("RETURN value: %d%n", s.getInt(1));  
}

I don't have a Sybase database available for testing, but I verified that this works with jTDS and SQL Server.

Upvotes: 1

Related Questions