Sidebar

Using a Stored Procedure that Returns Multiple Result Sets in QIE?

0 votes
355 views
asked Jul 20, 2021 by rich-c-2789 (16,180 points)
How can I call a stored procedure in QIE that returns multiple result sets?

2 Answers

0 votes
 
Best answer

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?

answered Jul 20, 2021 by rich-c-2789 (16,180 points)
edited Jul 20, 2021 by rich-c-2789
0 votes

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 CustomersSalesStoresByCity
   @City nvarchar(30)
AS
BEGIN
    SELECT FirstName as CustFirst, LastName as CustLast FROM Sales.vIndividualCustomer where city = @City
    SELECT FirstName as SalesFirst, LastName as SalesLast FROM Sales.vSalesPerson where city = @City
    SELECT Name as StoreName FROM Sales.vStoreWithAddresses where city = @City
END;

This stored procedure returns the results from the three queries to find customers, sales persons, and stores located in the same city.

answered Jul 20, 2021 by rich-c-2789 (16,180 points)
...