Using PreparedStatements and CallableStatements - Java Database Programming Bible - page 354

Figure 13-2: Member-registration form with user data restored and error message displayed for
user name

Using Stored Procedures with Input and Output Paramete rs

In addition to supplying input parameters to a stored procedure, you can get output parameters from a
stored procedure. If you decide to use an output parameter, it must be registered as an OUT parameter
using the CallableStatement.registerOutParameter() method before the execute method is
called. Here's an example:
cstmt.registerOutParameter(1, java.sql.Types.VARCHAR);

OUT parameter values can be retrieved after execution using get methods appropriate to the data types
of the values. Because of limitations some relational database management systems impose, all of the
results the execution generates of a CallableStatement object should be retrieved before OUT
parameters are retrieved.

Listing 13-9 gives an example of a simple stored procedure that checks a user name and password
against the database, returning the String "PASS" if a match is found or "FAIL" otherwise.

Listing 13-9: Using an output parameter with a stored procedure

CREATE PROCEDURE CHECK_USER_NAME
@UserName varchar(30),
@Password varchar(20),
@PassFail varchar(20) OUTPUT
As
IF EXISTS(Select * From Login
Where UserName = @UserName
And
Password = @Password)
SELECT @PassFail = 'PASS'
else
SELECT @PassFail = 'FAIL';

Note

Stored procedures can contain more than one SQL statement, in which case they
produce multiple results, and the execute method should be used. In cases where a
CallableStatement object returns multiple ResultSet objects, all of the results
should be retrieved using the method getMoreResults before OUT parameters are
retrieved.