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 specifically state something like RETURN 0 in your SQL.

EDIT 2: executeUpdate() returns an int but this int is not the same as the Return Value. Also, an OUT parameter is not the same as a return value.

Solution 1

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.

Solution 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.)