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?