Using a Stored Procedure with a Return Status in QIE?

Question

How can I call stored precedures in QIE that have a return status?

Answer 1

Another type of procedure that you can call is one that returns a status or a result parameter. This status is typically used to indicate the success or failure of the stored procedure.

QIE provides the pQuery.doQuery() method, which you can use to call this kind of stored procedure and to process the data that it returns.

When you create the pQuery object with the stored procedure "CALL Escape Sequence" specify the return status parameter by creating a named parameter that starts with either : or @ character. This acts as a placeholder for the parameter value that will be returned from the stored procedure. To specify a value for a return status parameter, you must specify the data type of the parameter by using the registerOutput_____ methods of the pQuery object before you run the stored procedure.

The value that you specify for the return status parameter in the registerOutput_____ method must be the same data type of the method used.

When you use a registerOutput_____ method for a return status parameter, you must specify the name of the named parameter used in the qie.getParameterizedQuery() method.

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?

Example: The return status parameter returns an int so we use pQuery.registerOutputInt(':employeeId').

//Create the pQuery object with the stored procedure to be called
var pQuery = qie.getParameterizedQuery("{ :isCityUsed = CALL [dbo].[CheckContactCity](:cityToCheck) }");

// Set the values for the input parameters
pQuery.setString("cityToCheck", qie.evaluateTemplate("Bothell"));

// Register the return status parameter
pQuery.registerOutputInt('isCityUsed');

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

// Get the value from the return status parameter
message.setNode("/", pQuery.getOutputParameter('isCityUsed'));

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-a-return-status?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 stored procedure called in this question. Use the following to create the stored procedure. I used the sample AdventureWorks2017 database from microsoft.

CREATE OR ALTER PROCEDURE CheckContactCity
   (@cityName CHAR(50))
AS
BEGIN
   IF ((SELECT COUNT(*)
   FROM Person.Address
   WHERE City = @cityName) > 1)
   RETURN 1
ELSE
   RETURN 0
END

This stored procedure returns a status value of 1 or 0, depending on whether the city that is specified in the cityToCheck parameter is found in the Person.Address table.