Using a stored procedure with a return status

Download JDBC driver

A SQL Server stored procedure that you can call is one that returns a status or a result parameter. This status is typically used to indicate the success or failure of the stored procedure. The Microsoft JDBC Driver for SQL Server provides the SQLServerCallableStatement class, which you can use to call this kind of stored procedure and to process the data that it returns.

When you call this kind of stored procedure by using the JDBC driver, you have to use the call SQL escape sequence in conjunction with the prepareCall method of the SQLServerConnection class. The syntax for the call escape sequence with a return status parameter is the following:

{[?=]call procedure-name[([parameter][,[parameter]]...)]}

When you construct the call escape sequence, specify the return status parameter by using the ? (question mark) character. This character acts as a placeholder for the parameter value that will be returned from the stored procedure. To specify a value for a return status parameter, you must specify the data type of the parameter by using the registerOutParameter method of the SQLServerCallableStatement class, before executing the stored procedure.

In addition, when you pass a value to the registerOutParameter method for a return status parameter, you must specify not only the data type to be used for the parameter, but also the parameter's ordinal placement in the stored procedure call. In the case of the return status parameter, its ordinal position will always be 1 because it is always the first parameter in the call to the stored procedure. Although the SQLServerCallableStatement class provides support for using the parameter's name to indicate the specific parameter, you can use only a parameter's ordinal position number for return status parameters.

As an example, create the following stored procedure in the AdventureWorks2022 sample database:

CREATE PROCEDURE CheckContactCity  
   (@cityName CHAR(50))  
AS  
BEGIN  
   IF ((SELECT COUNT(*)  
   FROM Person.Address  
   WHERE City = @cityName) > 1)  
   RETURN 1  
ELSE  
   RETURN 0  
END  

This stored procedure returns a status value of 1 or 0, depending on whether the city that is specified in the cityName parameter is found in the Person.Address table.

In the following example, an open connection to the AdventureWorks2022 sample database is passed in to the function, and the execute method is used to call the CheckContactCity stored procedure:

public static void executeStoredProcedure(Connection con) {
    try(CallableStatement cstmt = con.prepareCall("{? = call dbo.CheckContactCity(?)}");) {
        cstmt.registerOutParameter(1, java.sql.Types.INTEGER);
        cstmt.setString(2, "Atlanta");
        cstmt.execute();
        System.out.println("RETURN STATUS: " + cstmt.getInt(1));
    }
    // Handle any errors that may have occurred.
    catch (SQLException e) {
        e.printStackTrace();
    }
}

See also

Using statements with stored procedures