How to configure stored procedures output parameters in execute stored procedures smart service to get data output from DB
Discussion posts and replies are publicly visible
In your Execute Stored Procedure smart service, you will configure procedure outputs as Node Inputs. E.g. if you have a value returned from the procedure as "result", you will create an Input as "result", then utilize the Save Into setting to store this to a PV. If this is not working, please share details on your current configuration if possible.
I had created inputs as you told, but its not working.
Can you share the screenshot of your node configuration?
These are the two outputs i need, configured them as inputs
To confirm, which parameter is the input to the stored procedure (as you noted 2 are outputs, there are 3 parameters total). The screen shots show both D_CASE_ID and D_CLCTN_AGNCY are setup to be procedure outputs, but I would expect error_msg to be one of the outputs and one of the above should be an input?
If one of those 2 is a procedure input, I would expect you to populate something for the value on the right, to get it into the procedure call.
I posted more detail from the documentation about this in this other similar recent thread. Overall my suggestion would be that if your SP is only for querying, and not for modifying data, you might consider handling the query SP call in an Expression Rule instead of in the node, as it's much more straightforward to grab (and then deal with) the output from the Rule version of execute stored procedure.
I would agree to first look at the fn!executestoredprocedure() node over the smart service. However there is a trade off in performance - if you are utilizing a number of calls at once, such as to validate each row of a grid, you will note better performance with the smart service. This is due to the function not having the same Run Validation parameter that is available in the service, which you can set to false so that the call does not re-validate the procedure data types, etc, on each evaluation. The function will automatically re-validate the procedure during each call and you will start to notice performance degradation if you call it multiple times at once.
Saying that, I also prefer the function for one-at-a-time calls to a stored procedure.
One additional note to include, we have had a few situations where we could not use the smart service (MNI limitation), and needed to perform a number of stored procedure calls with the function, at one time, validating multiple rows of a grid. Performance was not great, so we actually setup a web service that would call the stored procedure instead - from the interface, we call the web service, bypassing this plugin, and performance is actually phenomenal. We get something like 1000 calls in 1 second (depends how well your procedure executes of course).