Output parameter of "Execute Stored Procedure" smart service

Per the documentation provided with the plug-in:

In other words, you'll need a CDT that exactly matches the columns in the output data set, and an ACP that either matches an OUT parameter of the stored procedure or otherwise named like "resultSet1" etc.

Edit to add: an easier method to get and deal with the results of a stored-procedure backed query (assuming you're querying only, and not writing/modifying data) would be to put your SP read in an expression rule, and not use the Execute Stored Procedure process node at all.  In the expression rule you'd use the executeStoredProcedure function which is a little more lenient in its output - it simply spits out a data dictionary that you can parse and then deal with as you like.

Per the documentation provided with the plug-in:

In other words, you'll need a CDT that exactly matches the columns in the output data set, and an ACP that either matches an OUT parameter of the stored procedure or otherwise named like "resultSet1" etc.

Edit to add: an easier method to get and deal with the results of a stored-procedure backed query (assuming you're querying only, and not writing/modifying data) would be to put your SP read in an expression rule, and not use the Execute Stored Procedure process node at all.  In the expression rule you'd use the executeStoredProcedure function which is a little more lenient in its output - it simply spits out a data dictionary that you can parse and then deal with as you like.

Thank you for your response.

Using executeStoredProcedure function I have able to get the result set. But the parameter inputs are not working and the procedure is returning all records as it returns the same if it's parameter values are null or not supplied.

I have executed the function like this:

executestoredprocedure(
  dataSourceName: "jdbc/DB_TEST",
  procedureName: "analytics.GetEmployee",
  inputs: {
  departmentId: 2,
  employeeId: 589
  }
) 

It is returning all records ignoring the parameter values. Am I doing any mistake?

Can you please provide an example of calling this method using more than one parameters?

The inputs need to be a dictionary (i believe the plugin comes with a custom type "ProcedureInput"), with the parameters "name" and "value", where "name" exactly matches the procedure's input, and "value" contains the value being passed in.

Here's an example of one being used in my current system, to give you a more solid idea:

And this would be your setup modified to dictionary values for input:

executestoredprocedure(
  dataSourceName: "jdbc/DB_TEST",
  procedureName: "analytics.GetEmployee",
  inputs: {
    {name: "departmentId", value: 2},
    {name: "employeeId", value: 589}
  }
) 

Thank you for your example. Now it is working fine. 

Thank you for your example. Now it is working fine.