How to Call Stored Procedures with OUTPUT Parameters in Java

Summary: in this tutorial, you will learn how to call a stored procedure with an OUTPUT parameter in SQL Server from a Java program.

This tutorial begins where the Calling a Stored Procedure in Java tutorial left off.

Creating a stored procedure

Step 1. Launch Microsoft SQL Server Management Studio (SSMS) and connect to the SQL Server.

Step 2. Execute the following statement to create a new stored procedure named createOrder with an output parameter:

CREATE PROCEDURE CreateOrder
    @CustomerId INT,
    @BookId INT,
    @Quantity INT,
    @Price DECIMAL(18, 2),
    @OrderDate DATE,
    @OrderId INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        -- Start a transaction
        BEGIN TRANSACTION;

        -- Check inventory
        DECLARE @CurrentQty INT;
        SELECT @CurrentQty = Qty FROM Inventories WHERE BookId = @BookId;

        IF @CurrentQty IS NULL OR @CurrentQty < @Quantity
        BEGIN
            RAISERROR('Insufficient inventory', 16, 1);
            ROLLBACK TRANSACTION;
            RETURN;
        END

        -- Insert into Orders table
        INSERT INTO Orders (OrderDate, CustomerId, TotalAmount)
        VALUES (@OrderDate, @CustomerId, @Price * @Quantity);

        -- Get the generated OrderId
        SET @OrderId = SCOPE_IDENTITY();

        -- Insert into OrderDetails table
        INSERT INTO OrderDetails (OrderId, BookId, Quantity, Price)
        VALUES (@OrderId, @BookId, @Quantity, @Price);

        -- Update the inventory
        UPDATE Inventories
        SET Qty = Qty - @Quantity
        WHERE BookId = @BookId;

        -- Commit the transaction
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        -- Handle errors and rollback the transaction
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END

        -- Return error information
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
    END CATCH
ENDCode language: SQL (Structured Query Language) (sql)

The CreateOrder stored procedure does the following:

Validate Inventory: Ensure inventory is sufficient by checking the inventory quantity with the order quantity.
Insert Order: Insert a new row into the Orders table and retrieve the generated order Id.
Insert OrderDetails: Insert a corresponding row in the OrderDetails table.
Update Inventory: Reduce the inventory quantity by the order quantity by updating the Inventories table.
Return Order Id: return the newly inserted order Id using an output parameter.

Calling the stored procedure with an OUTPUT parameter

Step 1. Define a new method createOrder in the OrderDB class that calls the CreateOrder stored procedure:

public int createOrder(int customerId, int bookId, int orderQty, double bookPrice, LocalDate orderDate) throws DBException {
  var sql = "{CALL CreateOrder(?, ?, ?, ?, ?, ?)}";
  try (var stmt = connection.prepareCall(sql)) {
    // Set input parameters
    stmt.setInt(1, customerId);
    stmt.setInt(2, bookId);
    stmt.setInt(3, orderQty);
    stmt.setDouble(4, bookPrice);
    stmt.setDate(5, Date.valueOf(orderDate));

    // Register the output parameter
    stmt.registerOutParameter(6, Types.INTEGER);

    // Execute the stored procedure
    stmt.execute();

    // Retrieve the output parameter (OrderID)
    return stmt.getInt(6);
  } catch(SQLException e) {
    throw new DBException(e.getMessage());
  }
}Code language: Java (java)

How it works.

First, construct a statement that calls the CreateOrder stored procedure:

var sql = "{CALL CreateOrder(?, ?, ?, ?, ?, ?)}";Code language: Java (java)

Second, create a CallableStatement object by calling the prepareCall() method of the Connection object:

try (var stmt = connection.prepareCall(sql)) {Code language: Java (java)

Third, set the input parameters for the stored procedure call statement:

stmt.setInt(1, customerId);
stmt.setInt(2, bookId);
stmt.setInt(3, orderQty);
stmt.setDouble(4, bookPrice);
stmt.setDate(5, Date.valueOf(orderDate));Code language: Java (java)

Fourth, register an output parameter with type integer:

stmt.registerOutParameter(6, Types.INTEGER);Code language: Java (java)

Fifth, execute the stored procedure to create a new order:

stmt.execute();Code language: Java (java)

Sixth, retrieve the order Id and return it:

return stmt.getInt(6);Code language: Java (java)

Finally, throw a new DBException if any error occurs while calling the stored procedure:

} catch(SQLException e) {
  throw new DBException(e.getMessage());
}Code language: Java (java)

Step 2. Modify the main() method of the Main class to call the createOrder method of the OrderDB class to create a new order:

import java.sql.SQLException;
import java.time.LocalDate;

public class Main {
    public static void main(String[] args) {
        // Initialize order data
        int customerId = 1;
        int bookId = 2;
        int orderQty = 3;
        double bookPrice = 29.99;
        var orderDate = LocalDate.of(2024, 7, 27);

        // Connect to the SQL Server
        try (var connection = SQLServerConnection.connect()) {
            var orderDB = new OrderDB(connection);
            // Create a new order
            var orderId = orderDB.createOrder(customerId, bookId, orderQty, bookPrice, orderDate);

            System.out.println("A sales order was created with the id: " + orderId);

        } catch (SQLException | DBException e) {
            System.err.println(e.getMessage());
        }
    }
}Code language: Java (java)

How it works.

First, initialize the order data:

var customerId = 1;
var bookId = 2;
var orderQty = 3;
var bookPrice = 29.99;
var orderDate = LocalDate.of(2024, 7, 27);Code language: Java (java)

Second, connect to the SQL Server:

try (var connection = SQLServerConnection.connect()) {Code language: Java (java)

Third, create a new OrderDB object:

var orderDB = new OrderDB(connection);Code language: Java (java)

Fourth, create a new order by calling the createOrder() method of the orderDB object:

var orderId = orderDB.createOrder(customerId, bookId, orderQty, bookPrice, orderDate);Code language: Java (java)

Fifth, display the order Id:

System.out.println("A sales order was created with the id: " + orderId);Code language: Java (java)

Finally, show the error message if any exception occurs while creating a new order:

} catch(SQLException | DBException e) {
  System.err.println(e.getMessage());
}Code language: Java (java)

Step 3. Run the Java program.

It’ll return the following output:

A sales order was created with the id: 2Code language: Java (java)

The output indicates that the order was created with the id 2.

Download the project source code

Summary

Use the CallableStatement to call a stored procedure in SQL Server from Java.
Use the registerOutParameter() method of the CallableStatement object to register an output parameter.
Call the get* method of the CallableStatement object to retrieve the value of the output parameter.

Was this tutorial helpful?