How can I run a Stored Procedure in E3 that passes input data and reads output data?
Solution:
To do so, follow these procedures:
1. Declare the output variable:
DECLARE @output int
2. Use EXEC command, passing the input value and informing the output:
EXEC SP_INSERT_ASSEMBLER ‘<¨%output_variable%>‘, @output OUTPUT
3. Create an alias for the return field:
SELECT @output as result
The whole code:
DECLARE @output int
EXEC SP_INSERT_ASSEMBLER ‘<¨%input_variable%>‘, @output OUTPUT
SELECT @output as result
NOTE: In case the stored procedure has another output result, such as INSERT, for example, E3 will return null, with no output value. This happens because E3 only has access to the first result in a query, and in this case, the aforementioned result is INSERT, which is null. To work around this, just set SET NOCOUNT ON command before executing the stored procedure, and then set it off at the end. This command turns off the database’s return messages regarding the number of record affected by commands INSERT and UPDATE, and thus makes way E3Query to receive the return value of the stored procedure.
Example:
SET NOCOUNT ON
DECLARE @output int
EXEC SP_INSERT_ASSEMBLER ‘<¨%input_variable%>‘, @output OUTPUT
SELECT @output as result
SET NOCOUNT OFF
