Using a Stored Procedure with Output Parameters in QIE?

Question

How can I call stored procedures in QIE with output parameters?

Answer 1

Another type of stored procedure that can be called is one that returns one or more OUT parameters, which are parameters that the stored procedure uses to return data back to the calling application.

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

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

When you use a registerOutput_____ method for an OUT 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 OUT 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("{ CALL [dbo].[GetImmediateManager](:employeeId, :managerIdOut) }");

// Set the values for the input parameters
pQuery.setInt("employeeId", qie.evaluateTemplate("10"));

// Register the output parameters
pQuery.registerOutputInt('managerIdOut');

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

// Get the value from the registered output parameter
message.setNode("/", pQuery.getOutputParameter('managerIdOut'));

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?

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-output-parameters?view=sql-server-2017

Addition 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 GetImmediateManager
   @employeeID INT,
   @managerID INT OUTPUT
AS
BEGIN
    SELECT @managerID = manager.[BusinessEntityID]
    FROM [HumanResources].[Employee] as employee
                INNER JOIN [HumanResources].[Employee] as manager
                ON employee.[OrganizationNode].GetAncestor(1) = manager.[OrganizationNode]
    WHERE employee.[BusinessEntityID] = 10
END

This stored procedure returns a single OUT parameter (managerID), which is an integer, based on the specified IN parameter (employeeID), which is also an integer. The value that is returned in the OUT parameter is the ManagerID based on the EmployeeID that is contained in the HumanResources.Employee table.