Best practice for getting ResultSet from MS SQL stored procedure?

Dear Experts,

Due to technical difficulties, I'm trying to circumvent a PreparedStatement and instead generate and use an MS SQL stored procedure directly.  However, I've never done it in Java.

I know how to write the stored procedure itself.  But what is the preferred syntax (other than PreparedStatement) to call it and return the ResultSet?

Who is Participating?
I just use PreparedStatements with MySQL and Postgres

Maybe that works in MSSQL as well (I've never tried)

     prepareStatement(  "CALL my_function( ?, ? )" ) ;

for example...
CallableStatement is the way to go.

Suppose the procedure returns an int value and that the first two parameters are IN (String and int), and the last is OUT (float). Then you should do:

callableStatement  = connection.prepareCall(" { ? = CALL procname(?, ?, ?) }");

callableStatement.registerOutParameter(1, java.sql.Types.INTEGER);
callableStatement.setString(2, "string");
callableStatement.setInt(3, 100);
callableStatement.registerOutParameter(4, java.sql.Types.FLOAT);

Then, after you execute the procedure, you can retrieve the return value and the out parameter value.

resultSet = callableStatement.executeQuery();

int returnValue = callableStatement.getInt(1); // get the return value
float outParameter = callableStatement.getFloat(4); // get the out parameter value

BrianMc1958Author Commented:
Thanks a lot, folks.  Works very nicely.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.