Getting the Return Value from JDBC MSSQL

Question

I'm connecting to SQL Server (2005) through Java using the Microsoft SQL Server JDBC Driver 2.0.

How do I get the return value from a stored procedure? I'm doing something like:

Connection connection = dataSource.getConnection()
CallableStatement proc = connection.prepareCall("{ call dbo.mySproc() }");
proc.execute();

Should I be using execute()? executeQuery()? executeUpdate()? None of these seem to return a return value by default but I'm not really sure how to get to it.

EDIT 1: To be clear, I know how to call stored procedures. This question is specifically about how to get the RETURN VALUE (as opposed to a Result Set). The Return Value is an integer that is usually generated when you execute a query with no Result Set or if you specificalyl state something like RETURN 0 in your SQL.

EDIT 2: executeUpdate() returns an int but will not return the Return Value.

Answer 1

Check the following links

http://www.exampledepot.com/egs/java.sql/CallProcedure.html

http://www.jguru.com/faq/view.jsp?EID=30731

Answer 2

c.prepareCall("? = ..");
cs.execute();
String returnedValue = cs.getString(1);

(or the method of the appropriate type. You can use getObject alternatively)

From an old getting started tutorial

the getXXX methods in CallableStatement retrieve values from the OUT parameters and/or return value of a stored procedure.

(Btw, the links that were provided by Umesh had this sort of information.)

Answer 3

Bozho's 2nd revised answer was close but not quite there. It did lead me to the answer though.

Taking the code example I started with we end up with:

CallableStatement proc = connection.prepareCall("{ ? = call dbo.mySproc() }");
proc.registerOutParameter(1, Types.INTEGER);
proc.execute();
int returnValue = proc.getInt(1);

The key pieces here are the "? =" in front of the "call" in the prepareCall function which sets up a place for the return value and the registerOutputParameter. It has to be registered as an Integer, as the return value is always an int (at least in SQL Server, maybe it's different in other DBs). You therefore have to get it using getInt. I tested this method and it does work.