Using a Stored Procedure with an Update Count in QIE?

Question

How can I call stored procedures in QIE that return an update count?

Answer 1

To modify data in a database by using a stored procedure, QIE provides the pQuery.doUpdateQuery() method. By using the pQuery.doUpdateQuery() method, 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.

The pQuery.doUpdateQuery() method will return an int value that contains the number of rows affected by the stored procedure.

To call a stored procedure in QIE using the "CALL escape sequence" syntax, first replace the curly braces with there "numeric character sequence" equivalents. See How to escape curly braces when calling stored procedures?

In the following example, the pQuery.doUpdateQuery() is used to call the UpdateTestTable stored procedure and return a count of the rows that are affected by the stored procedure.

    //Create the pQuery object with the stored procedure to be called
    var pQuery = qie.getParameterizedQuery("{ CALL [dbo].[UpdateTestTable](:stringParam, :intParam) }");

    // Set the values for the input parameters
    pQuery.setString("stringParam", qie.evaluateTemplate("foo"));
    pQuery.setInt("intParam", 42);

    // Execute the stored procedure
    var queryResult = pQuery.doUpdateQuery(
       "AdventureWorks2017"
    );

    // Get the update count
    message.setNode("/", queryResult);

This questions was inspired and adapted to QIE from this article:

https://docs.microsoft.com/en-us/sql/connect/jdbc/using-a-stored-procedure-with-an-update-count?view=sql-server-2017

Note: Since we use evaluateTemplate in the set method above we could replace "10" with a node tag to be evaluated. See What is a nodetag?

See Recommendations calling MSSQL stored procedures without SET NOCOUNT ON?

Additional examples can be found in this question:

How to call a stored procedures in QIE?

Answer 2

This is the sample table, insert, and stored procedure called in this question. Use the following to create the table, insert a record, and create the stored procedure. I used the sample AdventureWorks2017 database from microsoft.

CREATE TABLE TestTable
   (Col1 int IDENTITY,
    Col2 varchar(50),
    Col3 int);

INSERT INTO dbo.TestTable (Col2, Col3) VALUES ('b', 10);

CREATE OR ALTER PROCEDURE UpdateTestTable
   @Col2 varchar(50),
   @Col3 int
AS
BEGIN
   UPDATE TestTable
   SET Col2 = @Col2, Col3 = @Col3
END;

This stored procedure returns an update count for the rows affected by the update statement in the stored procedure.