Best practice for getting ResultSet from MS SQL stored procedure?

Posted on 2007-10-04
Last Modified: 2008-01-09
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?

Question by:BrianMc1958
    LVL 20

    Accepted Solution

    LVL 35

    Expert Comment

    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...
    LVL 9

    Assisted Solution

    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


    Author Comment

    Thanks a lot, folks.  Works very nicely.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    linearIn  challenge 23 54
    JVM encoding. How to change encoding. 27 55
    Modeling a class in java 5 23
    create a gui in perl 3 22
    Introduction Java can be integrated with native programs using an interface called JNI(Java Native Interface). Native programs are programs which can directly run on the processor. JNI is simply a naming and calling convention so that the JVM (Java…
    Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
    Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
    Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now