Using a Stored Procedure with an Update Count

 

DownloadDownload JDBC Driver

To modify data in a SQL Server database by using a stored procedure, the Microsoft JDBC Driver for SQL Server provides the SQLServerCallableStatement class. By using the SQLServerCallableStatement class, you can call stored procedures that modify data that is contained in the database and return a count of the number of rows affected, also referred to as the update count.

After you have set up the call to the stored procedure by using the SQLServerCallableStatement class, you can then call the stored procedure by using either the execute or the executeUpdate method. The executeUpdate method will return an int value that contains the number of rows affected by the stored procedure, but the execute method does not. If you use the execute method and want to get the count of the number of rows affected, you can call the getUpdateCount method after you run the stored procedure.

System_CAPS_ICON_note.jpg Note


If you want the JDBC driver to return all update counts, including update counts returned by any triggers that may have fired, set the lastUpdateCount connection string property to "false". For more information about the lastUpdateCount property, see Setting the Connection Properties.

As an example, create the following table and stored procedure, and also insert sample data in the 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;  
INSERT INTO dbo.TestTable (Col2, Col3) VALUES ('b', 10);  

In the following example, an open connection to the AdventureWorks sample database is passed in to the function, the execute method is used to call the UpdateTestTable stored procedure, and then the getUpdateCount method is used to return a count of the rows that are 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();
   }
}

Using Statements with Stored Procedures

Community Additions

ADD
Show: