Detailed description of stored procedures for calling SQL Server using Java-calling stored procedures with input parameters

2. Call the stored procedure with input parameters

When you use a JDBC driver to call a stored procedure with parameters, you must use the call SQL escape sequence in conjunction with the preparecall METHOD OF THE sqlserverconnection class. The syntax for the call escape sequence with the in parameter is as follows:

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

When constructing the call escape sequence, use? (Question mark) character to specify the in parameter. This character acts as a placeholder for the parameter values to be passed to the stored procedure. You can use one of the setter methods of the sqlserverpreparedstatement class to specify a value for the parameter. The applicable setter method is determined by the Data Type of the in parameter.

When passing a value to the setter method, you must specify not only the actual value to be used in the parameter, but also the ordinal position of the parameter in the stored procedure. For example, if a Stored Procedure contains a single in 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.

Use the uspgetemployeemanagers stored procedure in the SQL Server 2005 adventureworks sample database as an example of how to call a stored procedure containing the in parameter. This stored procedure accepts a single input parameter named employeeid (which is an integer) and returns a recursive list of employees and their managers based on the specified employeeid. The following is the Java code that calls this stored procedure:

Java code:

Public static void executesprocinparams (connection con ){

Try {

Preparedstatement pstmt = con. preparestatement ("{call DBO. uspgetemployeemanagers (?)} ");

Pstmt. setint (1, 50 );

Resultset rs = pstmt.exe cutequery ();

While (Rs. Next ()){

System. Out. println ("Employee :");

System. Out. println (Rs. getstring ("lastname") + "," + Rs. getstring ("firstname "));

System. Out. println ("Manager :");

System. Out. println (Rs. getstring ("managerlastname") + "," + Rs. getstring ("managerfirstname "));

System. Out. println ();

}

Rs. Close ();

Pstmt. Close ();

}

Catch (exception e ){

E. printstacktrace ();

}

}

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