Java calls SQL Server for a detailed description of stored procedures

Reprinted from Microsoft official documentation http://msdn2.microsoft.com/zh-cn/library/ms378995.aspx
Included in Www.enjoyjava.net/f25

This article is longer and contains the following sections

2. Using stored procedures with no parameters
4. Using a stored procedure with input parameters
6. Using a stored procedure with output parameters
8. Using a stored procedure with a return state
9. using a stored procedure with an update count
   

   1 using stored procedures with no parameters

   When you invoke a stored procedure without parameters using the JDBC driver, you must use the call SQL escape sequence. The syntax for a call escape sequence without parameters is as follows:
   {Call Procedure-name}
   As an instance, create the following stored procedure in the SQL Server 2005 AdventureWorks Sample database:

   CREATE PROCEDURE Getcontactformalnames
   As
   BEGIN
   SELECT TOP Ten Title + ' + FirstName + ' + LastName as Formalname
   From Person.Contact
   End this stored procedure returns a single result set that contains a column of data (consisting of the salutation, name, and last names of the first 10 contacts in the Person.Contact table).
   
   In the following instance, an open connection to the AdventureWorks sample database is passed to this function, and then using theExecuteQueryThe Getcontactformalnames method invokes the stored procedure.
   public static void Executesprocnoparams (Connection con){
   Try {
   Statement stmt = Con.createstatement ();
   ResultSet rs = stmt.executequery ("{call dbo. Getcontactformalnames} ");
   
   while (Rs.next ()) {
   System.out.println (rs.getstring ("Formalname"));
   }
   Rs.close ();
   Stmt.close ();
   }
   catch (Exception e) {
   E.printstacktrace ();
   }
   }

   2 using a stored procedure with input parameters

   When you use the  JDBC  driver to invoke stored procedures with parameters, you must combine   sqlserverconnection preparecall   method uses  call SQL  escape sequence. The syntax for the  call  escape sequence with the  IN  parameter is as follows:  
   {call procedure-name[([parameter][,[parameter]) ...)]}  

   When constructing a call escape sequence, use the? (question mark) character to specify the in parameter. This character acts as a placeholder for the parameter value to pass to the stored procedure. You can use one of the setter methods of the Sqlserverpreparedstatement class to specify a value for the parameter. The setter method that can be used is determined by the data type of the in parameter.
   When passing a value to a setter method, you need to 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, its ordinal value is 1. If a stored procedure contains two parameters, the first ordinal value is 1, and the second ordinal value is 2.
   As an instance of how to invoke a stored procedure that contains an in parameter, use the SQL Server 2005 AdventureWorks uspGetEmployeeManagers stored procedure in the sample database. This stored procedure takes a single input parameter named EmployeeID, which is an integer value, and then 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:

   
   public static void Executesprocinparams (Connection con){
   Try{
   PreparedStatement pstmt = con.preparestatement ("{Call Dbo.uspgetemployeemanagers (?)}");
   Pstmt.setint (1, 50);
   ResultSet rs = Pstmt.executequery ();
   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 ();
       }
   }

   3 Using a stored procedure with output parameters

   When you call such a stored procedure using the JDBC driver, you must combinesqlserverconnectionClass ofPreparecallMethod uses the call SQL escape sequence. The syntax for a call escape sequence with an out parameter is as follows:
   {Call procedure-name[([Parameter][,[parameter]] ...)}
   
   When constructing a call escape sequence, use the? (question mark) character to specify the out parameter. This character acts as a placeholder for the value of the parameter to be returned from the stored procedure. To specify a value for an out parameter, you must use it before running the stored proceduresqlservercallablestatementClass ofRegisteroutparameterMETHOD specifies the data type of each parameter.
   
   UseRegisteroutparameterThe value specified by the method for the out parameter must be one of the JDBC data types that java.sql.Types contains, and it is also mapped to one of the cost-of-SQL Server data types. For more information about JDBC and SQL Server data types, seeUnderstanding the JDBC driver data type。
   
   When you give an out parameter to theRegisteroutparametermethod to pass a value, you must not only specify the data type to use for this parameter, but you must specify the ordinal position of the parameter or the name of the parameter in the stored procedure. For example, if a stored procedure contains a single out parameter, its ordinal value is 1, and 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), the stored procedure also returns a single integer out parameter (managerid). The value returned in the Employeeid,out parameter contained in the HumanResources.Employee table is ManagerID.
   
   
   In the following instance, an open connection to the AdventureWorks sample database is passed to this function, and then using theExecuteMethod calls the Getimmediatemanager stored procedure:

   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.execute ();
   System.out.println ("MANAGER ID:" + cstmt.getint (2));
   }
   catch (Exception e) {
   E.printstacktrace ();
   }
   }This example uses an ordinal position to identify the parameter. Alternatively, you can use the name of the parameter, rather than its ordinal 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 definition of the stored procedure: CREATE PROCEDURE Getimmediatemanager
   @employeeID INT,
   @managerID INT OUTPUT
   As
   BEGIN
   SELECT @managerID = ManagerID
   From HumanResources.Employee
   WHERE EmployeeID = @employeeID
   END

   The stored procedure may return an update count and multiple result sets. Microsoft SQL Server 2005 JDBC Driver follows the JDBC 3.0 specification, which specifies that multiple result sets and update counts should be retrieved before retrieving out parameters. That is, the application should retrieve all ResultSet objects and update counts first, and then use the callablestatement.getter method to retrieve the out parameters. Otherwise, when the out parameter is retrieved, the ResultSet object and the update count that have not been retrieved are lost.

   4 using a stored procedure with a return state

       When you call this stored procedure using the  JDBC  driver, you must combine the     class  preparecall   methods use  call  SQL  escape sequences. The syntax for returning the  call  escape sequence for a status parameter is as follows:  
   {[? =]call procedure-name[([Parameter][,[parameter]] ...)}  &NBSP
   
       construct  call  escape sequence, use the  ? (question mark) character to specify the return status parameter. This character acts as a placeholder for the value of the parameter to be returned from the stored procedure. To specify a value for the return status parameter, you must use &NBSP before executing the stored procedure; sqlservercallablestatement   class   registeroutparameter   method specifies the data type of the parameter.
   
       In addition, when passing a return status parameter value to   Registeroutparameter   method, you need not only specify the data type of the parameter to use, You must also specify the ordinal position of the parameter in the stored procedure. For a return status parameter, its ordinal position is always  1, because it is always the first parameter when a stored procedure is called. Although   the sqlservercallablestatement   class supports using the name of the parameter to indicate a specific parameter, but you can only use the ordinal position number of the parameter for the return state parameter.  
       as an instance, create the following stored procedure in the  SQL Server 2005 AdventureWorks  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

   The stored procedure returns a status value of 1 or 0, depending on whether the city specified by the CityName parameter can be found in table person.address.
   
   
   In the following instance, the open connection to the AdventureWorks sample database is passed to this function, and then the checkcontactcity stored procedure is invoked using the execute method:

   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));
   }
   Cstmt.close ();
   catch (Exception e) {
   E.printstacktrace ();
   }
   }

   5 using a stored procedure with update count

   After you use the Sqlservercallablestatement class to build a call to a stored procedure, you can use either the execute or the executeupdate method to invoke the stored procedure. The executeupdate method returns an int value that contains the number of rows affected by this stored procedure, but the execute method does not return this value. If you use the Execute method and you want to count the number of rows affected, you can call the Getupdatecount method After you run the stored procedure.

   As an instance, create the following tables and stored procedures in the SQL Server 2005 AdventureWorks Sample database:

   CREATE TABLE TestTable
   (Col1 int IDENTITY,
   Col2 varchar (50),
   Col3 int);
   
   CREATE PROCEDURE updatetesttable
   @Col2 varchar (50),
   @Col3 int
   As
   BEGIN
   UPDATE TestTable
   SET Col2 = @Col2, Col3 = @Col3
   END; In the following instance, the open connection to the AdventureWorks sample database is passed to this function and is used with theExecuteMethod calls the updatetesttable stored procedure and then uses theGetupdatecountmethod returns the Count of rows affected by the stored procedure. public static void Executeupdatestoredprocedure (Connection con){
   Try {
   CallableStatement cstmt = Con.preparecall ("{call dbo. Updatetesttable (?,?)} ");
   Cstmt.setstring (1, "A");
   Cstmt.setint (2, 100);
   Cstmt.execute ();
   int count = Cstmt.getupdatecount ();
   Cstmt.close ();
   
   System.out.println ("ROWS Affected:" + count);
   }
   catch (Exception e) {
   E.printstacktrace ();
   }
   }

Java calls SQL Server for a detailed description of stored procedures