Another type of stored procedure that can be called is one that contains one or more IN parameters, which are parameters that can be used to pass data into the stored procedure.
When you create the pQuery object with the stored procedure "CALL Escape Sequence" specify the IN parameters by creating a named parameter that starts with either a : or @ character. For example ":employeeId". This acts as a placeholder for the parameter values that will be passed into the stored procedure. To specify a value for a parameter, you can use one of the setter methods of the pQuery object. The setter method that you can use is determined by the data type of the IN parameter. In this case the stored procedure expects an int so we use the setInt method to set the value.
When you pass a value to the setter method, you must specify not only the actual value that will be used in the parameter, but also the name of the named parameter used in the qie.getParameterizedQuery() method.
As an example of how to call a stored procedure that contains an IN parameter, use the uspGetEmployeeManagers stored procedure in the AdventureWorks2017 database. This stored procedure accepts a single input parameter named EmployeeID, which is an integer value, and it returns a recursive list of employees and their managers based on the specified EmployeeID. The QIE code for calling this stored procedure is as follows:
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? https://www.qvera.com/kb/index.php/2702/how-to-escape-curly-braces-when-calling-stored-procedures?
//Create the pQuery object with the stored procedure to be called
var pQuery = qie.getParameterizedQuery("{ CALL [dbo].[uspGetEmployeeManagers](:employeeId) }");
// Set the values for the input parameters
pQuery.setInt("employeeId", qie.evaluateTemplate("10"));
// Execute the stored procedure
var queryResult = pQuery.doQuery(
"AdventureWorks2017"
);
// Get the results
message.setNode("/", queryResult.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?
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-input-parameters?view=sql-server-2017
Addition examples can be found in this question:
How to call a stored procedures in QIE?