To call stored procedures that returns multiple result sets, QIE provides the pQuery.callStoredProcedure() method. By using the pQuery.callStoredProcedure() method, you can call stored procedures that return one or multiple result sets.
The pQuery.callStoredProcedure() method will return an array of CSV Messages. Each returned result set can be accessed via an index into this array. The first result set is accessed with index 0.
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.callStoredProcedure() method is used to call the CustomersSalesStoresByCity stored procedure. It returns an array called queryResult with three CSV Messages. The order of the results are determined by the order in the stored procedure. Since the stored procedure executes the query for customers first, it is the first to be returned in the array. The results for customers are stored in the array at index 0 and accessed using queryResult[0]. Sales persons are accessed via queryResult[1]. Stores are accessed via queryResult[2].
//Create the pQuery object with the stored procedure to be called
var pQuery = qie.getParameterizedQuery("{ CALL [dbo].[CustomersSalesStoresByCity](:cityParam) }");
// Set the values for the input parameters
pQuery.setString("cityParam", qie.evaluateTemplate("redmond"));
// Execute the stored procedure
var queryResult = pQuery.callStoredProcedure(
"AdventureWorks2017"
);
// Get the results for Customers
messageCache.setValue('Customers', queryResult[0].getNode("/"));
// Get the results for Sales Persons
messageCache.setValue('SalesPersons', queryResult[1].getNode("/"));
// Get the results for Stores
messageCache.setValue('Stores', queryResult[2].getNode("/"));
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?