Using a Stored Procedure with No Parameters in QIE?

Question

How can I call a stored procedure in QIE that does not have any parameters?

Answer 1

The simplest kind of stored procedure that you can call is one that contains no parameters and returns a single result set.

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?

Here is an example using the api's for parameterized queries.

//Create the pQuery object with the stored procedure to be called
var pQuery = qie.getParameterizedQuery("{ CALL dbo.GetContactFormalNames }");

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

// Get the results
message.setNode("/", queryResult.getNode("/"));

This test channel uses a source node configured to use CSV and a discard node. It populates the CSV with the results.

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

See Recommendations calling MSSQL stored procedures without SET NOCOUNT ON?

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 GetContactFormalNames
AS
BEGIN
    SELECT TOP 10 TRIM(CONCAT(Title, ' ', FirstName, ' ', LastName)) AS FormalName
    FROM Person.Person
END

This stored procedure returns a single result set that contains one column of data, which is a combination of the title, first name, and last name of the top 10 records that are in the Person.Person table.