How to execute the store procedure and read output parameters using PostgreSQL ExecuteSql Task

Sometimes in PostgreSQL, you have a Stored Procedure that has INPUT Parameters as well as SOME OUTPUT Parameters also.

In this section, we will see how to execute the Stored Procedure and read OUTPUT parameters using PostgreSQL ExecuteSql Task and store the output values in the desired variable. You need to pass the null as the OUTPUT parameter value. 

Let's say you have 1 Stored Procedure with 2-INPUT Parameters and 2-OUTPUT Parameters. 

For example, we have the following test store procedure which has 2 INPUT and 2 OUTPUT parameters.

CREATE OR REPLACE PROCEDURE Test_proc 
(
  input1 IN varchar(10), 
  input2 IN INT, 
  output1 OUT INT, 
  output2 OUT varchar(10)
) 
LANGUAGE plpgsql 
AS  $$ 
DECLARE 
BEGIN 
  
  output2 := input1; 
  output1 := input2; 
  
END  $$

You need to pass the null as the OUTPUT parameter value. 

call Test_proc('Test',123,null,null);

Let's make the call in the pgAdmin4 Query tool and you will get the output like this:

Now let's make the Stored Procedure call using PostgreSQL ExecuteSql Task.

First all let's create the desired variables.

To store the output dataset, we need an object type variable and two other variables to store individual OUTPUT values.

Now let's configure the PostgreSQL ExecuteSql Task like this, Source Type: DirectValue, Result Type: FullResultset, Result Variable: {Create Object DataType Variable} and click on OK.

SQL Statement/Command to call Store procedure. 

call Test_proc('Test',123,null,null);

 

Below it needs to use the Foreach Loop Container and configure Collection like this.

Now in the Foreach Loop Container go to Variable Mappings and configure it like this to get the desired OUTPUT value in the desired variable.

That's it when you run the package it will make the store procedure call and set the output parameters values in the desired variable.

Using our ZS Loggin Task you can log the variable values in the Output logs to check it.

Contact Us

If you have more question(s) feel free to contact us via Live chat or email at [e-mail]

https://zappysys.com/support/