Detailed description of the stored procedure for Java to call SQL Server-call the stored procedure with output parameters

3. When using the JDBC driver to call such a stored procedure, the call SQL escape sequence must be used in conjunction with the preparecall METHOD OF THE sqlserverconnection class. The syntax for the call escape sequence with the out parameter is as follows:

{Call procedure-name [([parameter] [, [parameter]...)]}

When constructing the call escape sequence, use? (Question mark) character to specify the out parameter. This character acts as a placeholder for the parameter values returned from this stored procedure. To specify a value for the out parameter, you must use the registeroutparameter method of the sqlservercallablestatement class to specify the Data Type of each parameter before running the stored procedure.

The value specified for the out parameter using the registeroutparameter method must be one of the JDBC data types included in Java. SQL. types, and it is mapped to one of the SQL server data types at the cost. For more information about JDBC and SQL server data types, see learn about JDBC driver data types.

When you pass a value for the out parameter to the registeroutparameter method, you must not only specify the data type used for this parameter, in addition, you must specify the serial number location or name of this parameter during the stored procedure. For example, if a Stored Procedure contains a single out parameter, the ordinal value is 1. If the Stored Procedure contains two parameters, the first ordinal value is 1, and the second ordinal value is 2.

As an instance, create the following stored procedure in the SQL Server 2005 adventureworks Sample Database: according to the specified integer in parameter (employeeid), this stored procedure also returns a single integer out parameter (managerid ). According to the employeeid contained in the HumanResources. Employee table, the value returned by the out parameter is managerid.

In the following example, the open connection of the adventureworks sample database will be passed to this function, and then the getimmediatemanager stored procedure will be called using the execute method:

Java code:

Public static void executestoredprocedure (connection con ){

Try {

Callablestatement cstmt = con. preparecall ("{call DBO. getimmediatemanager (?, ?)} ");

Cstmt. setint (1, 5 );

Cstmt. registeroutparameter (2, java. SQL. types. integer );

Cstmt.exe cute ();

System. Out. println ("manager ID:" + cstmt. getint (2 ));

}

Catch (exception e ){

E. printstacktrace ();

}

}

In this example, the serial number position is used to identify the parameter. Alternatively, you can use the parameter name (rather than its serial number position) to identify this parameter. The following code example modifies the previous example to show how to use named parameters in a Java application. Note that these parameter names correspond to the parameter names in the Stored Procedure Definition:

SQL code create procedure getimmediatemanager

@ Employeeid int,

@ Managerid int output

As

Begin

Select @ managerid = managerid

From HumanResources. Employee

Where employeeid = @ employeeid

End

Stored Procedures may return update counts and multiple result sets. Microsoft SQL Server 2005 JDBC driver complies with the JDBC 3.0 specification, which specifies that multiple result sets and update counts should be retrieved before the out parameter is retrieved. That is to say, the application should first retrieve all the resultset objects and update counts, and then use callablestatement. getter to retrieve out parameters. Otherwise, when the out parameter is retrieved, the unretrieved resultset object and the update count will be lost.

From: http://www.cn-java.com/www1? Action-viewnews-itemid-55626