Connect to database and call stored procedure

Do you need help with your Java programming?

Click here for instant help with your Java code.

This code example shows how to connect to a database (in this case MS SQL Server) and call a stored procedure.
To call the stored procedure you create an instance of the class CallableStatement by calling the method prepareCall() on the Connection object.
In the first example below we assume that the name of the stored procedure is "generateID" and it takes one string argument, and returns an Id which depends on the argument (in the example it returns employeeId).
The value of the argument is set by calling the method setString() on the CallableStatement object. Since we want a return value from the stored procedure we have to call the executeQuery() method, which returns a ResultSet.

        Connection con = null;
        CallableStatement proc_stmt = null;
        ResultSet rs = null;

        try {

            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

            con = DriverManager.getConnection("jdbc:sqlserver://MYSERVER;databaseName=MYDATABASE", "USERID", "PASSWORD");

            proc_stmt = con.prepareCall("{ call generateID(?) }");

            proc_stmt.setString(1, "employee");
            rs = proc_stmt.executeQuery();

            if (rs.next()) {
                int employeeId = rs.getInt(1);
                System.out.println("Generated employeeId: " + employeeId);
            } else {
                System.out.println("Stored procedure couldn't generate new Id");
            }
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {

            try {

                rs.close();
                proc_stmt.close();
                con.close();

            } catch (SQLException ex) {
                ex.printStackTrace();
            }

        }
        //Coded by www.javadb.com

In the second example we do about just the same as above except that this time we don't provide an argument to the stored procedure and we don't receive any return value.

        Connection con = null;
        CallableStatement proc_stmt = null;

        try {

            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            con = DriverManager.getConnection("jdbc:sqlserver://MYSERVER;databaseName=MYDATABASE", "USERID", "PASSWORD");
            proc_stmt = con.prepareCall("{ call someStoredProc() }");

            proc_stmt.executeQuery();

        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {

            try {

                proc_stmt.close();
                con.close();

            } catch (SQLException ex) {
                ex.printStackTrace();
            }

        }
        //Coded by www.javadb.com

Do you know your Java?
Take a Ten-Question-Java-Quiz!

Search for code examples on this site

Need help with your Java code? It's secure and confidential.

This is how it works:

Send a detailed description of what you need help with, the more details the better. Also provide a deadline for when it has to be finished. More time means better chance of putting your request into the schedule.

If the request is serious you will shortly receive an email with the price, to which you have to respond if you accept.

Once you have accepted, the work will begin on developing your code by an experienced Java developer. When the code is finished a link to a secure payment will be sent to you.

The source code is then sent to you once the payment is completed.

IMPORTANT! The request needs to be very detailed, else it may be ignored.

Write your detailed request here:

E-mail address: