Calling Stored Procedures¶

QIE calls stored procedures through the same parameterized-query API as ordinary SELECT/INSERT/UPDATE statements (qie.getParameterizedQuery(...) plus a pQuery.set* for each input) using JDBC's { CALL ... } escape syntax. Different pQuery.* execute methods handle the variations: single result set, multiple result sets, output parameters, return status, and update counts.

Escape the curly braces

The CALL escape sequence includes literal { and }, which QIE's template engine interprets as node tags. In QIE script editors and templates, replace each { with { and each } with }. For example, write { CALL dbo.MyProc(:p) } rather than { CALL dbo.MyProc(:p) }. The CALL strings shown below use the literal braces for readability; convert them when you paste into a mapping script.

No parameters¶

For a procedure with no parameters that returns a single result set, use pQuery.doQuery(...) exactly as you would for a regular SELECT:

var pQuery = qie.getParameterizedQuery("{ CALL dbo.GetContactFormalNames }");
var queryResult = pQuery.doQuery("AdventureWorks2017");
message.setNode("/", queryResult.getNode("/"));

Input parameters¶

Declare each input parameter in the CALL string with a : (or @) prefix, then set its value with the matching pQuery.set* call before executing.

var pQuery = qie.getParameterizedQuery("{ CALL [dbo].[uspGetEmployeeManagers](:employeeId) }");
pQuery.setInt("employeeId", 10);
var queryResult = pQuery.doQuery("AdventureWorks2017");
message.setNode("/", queryResult.getNode("/"));

The setter used (setInt, setString, setDate, etc.) must match the column's data type. See the Code Wizard reference for the full list of set* methods on ParameterizedQuery.

Output parameters¶

For OUTPUT parameters, list each one in the CALL string alongside the inputs, register the output's type with pQuery.registerOutput*, execute, and read the value back with pQuery.getOutputParameter:

var pQuery = qie.getParameterizedQuery("{ CALL [dbo].[GetImmediateManager](:employeeId, :managerIdOut) }");
pQuery.setInt("employeeId", 10);
pQuery.registerOutputInt("managerIdOut");
pQuery.doQuery("AdventureWorks2017");
message.setNode("/", pQuery.getOutputParameter("managerIdOut"));

Return status¶

A stored procedure's RETURN value is read with the same registerOutput* + getOutputParameter pattern, with the return slot named on the left of = in the CALL string:

var pQuery = qie.getParameterizedQuery("{ :isCityUsed = CALL [dbo].[CheckContactCity](:cityToCheck) }");
pQuery.setString("cityToCheck", "Bothell");
pQuery.registerOutputInt("isCityUsed");
pQuery.doQuery("AdventureWorks2017");
message.setNode("/", pQuery.getOutputParameter("isCityUsed"));

Multiple result sets¶

For procedures that emit more than one SELECT, use pQuery.callStoredProcedure(...) instead of doQuery. It returns an array of CSV result-set messages indexed by execution order:

var pQuery = qie.getParameterizedQuery("{ CALL [dbo].[CustomersSalesStoresByCity](:cityParam) }");
pQuery.setString("cityParam", "Redmond");
var resultSets = pQuery.callStoredProcedure("AdventureWorks2017");
messageCache.setValue("Customers",   resultSets[0].getNode("/"));
messageCache.setValue("SalesPersons", resultSets[1].getNode("/"));
messageCache.setValue("Stores",       resultSets[2].getNode("/"));

Update counts¶

For procedures whose primary purpose is INSERT/UPDATE/DELETE, use pQuery.doUpdateQuery(...) to get back the number of affected rows as an integer:

var pQuery = qie.getParameterizedQuery("{ CALL [dbo].[UpdateTestTable](:stringParam, :intParam) }");
pQuery.setString("stringParam", "foo");
pQuery.setInt("intParam", 42);
var rowsAffected = pQuery.doUpdateQuery("AdventureWorks2017");
message.setNode("/", rowsAffected);

MSSQL stored procedures without `SET NOCOUNT ON`¶

MSSQL stored procedures that perform DML (INSERT/UPDATE/DELETE) before their final SELECT interleave row-affected counts with result sets when SET NOCOUNT is OFF (the default). Standard JDBC handling can return null or skip past the real result set in this case.

QIE 0.51 and later handle this transparently:

pQuery.doQuery(...) skips any returned update counts and returns the first result set as a CSV message.
pQuery.callStoredProcedure(...) skips update counts and returns every result set in the array.
A procedure that returns only update counts, with no result set at all, gives back null from both calls, because there are no columns to build a header row from. Use pQuery.doUpdateQuery(...) for those, as shown above.

Adding SET NOCOUNT ON to your procedures is still best practice (it avoids returning update counts in the first place) but is not required for QIE to read your procedures correctly when you use the methods above.