Skip to content

Latest commit

 

History

History
48 lines (36 loc) · 3.14 KB

using-a-stored-procedure-with-an-update-count.md

File metadata and controls

48 lines (36 loc) · 3.14 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic
Using a stored procedure with an update count
Learn how to use a stored procedure and return a count of the number of rows affected (also called the update count) using the JDBC Driver for SQL Server.
David-Engel
davidengel
08/12/2019
sql
connectivity
conceptual

Using a stored procedure with an update count

[!INCLUDEDriver_JDBC_Download]

To modify data in a [!INCLUDEssNoVersion] database by using a stored procedure, the [!INCLUDEjdbcNoVersion] provides the SQLServerCallableStatement class. By using the SQLServerCallableStatement class, you can call stored procedures that modify data that is 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 doesn't. 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.

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 [!INCLUDEssSampleDBnormal] 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 [!INCLUDEssSampleDBnormal] 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.

[!codeJDBC#UsingSprocWithUpdateCount1]

See also

Using statements with stored procedures