Skip to main content
MG_26021-Visitor
1-Visitor
Sep 9, 2022 12:15 PM UTC
Solved

How to pass infotable to stored procedure for inserting data in PostgreSQL

Hi,

 

How can we pass intotable as a parameter to a stored procedure for inserting data in postgreSQL table.

 

@nmilleson @VladimirRosu_116627 , Can you please help me.

Thanks,

Meghna

nmillesonAnswer
17-Peridot
Sep 9, 2022 12:49 PM UTC

@MG_2602 ,

 

Unfortunately we cannot pass infotable parameters to the Database Thing SQL services (see here).  You could iterate the infotable into a string that you could pass into a parameter.  Just make sure that the parameter in your service is surrounded by <<>> instead of [[ ]]. Something like:

 

CALL myProcedure(<<paramString>>);

  

-Nick

View in thread

3 replies

nmilleson17-PeridotAnswer
17-Peridot
Sep 9, 2022 12:49 PM UTC

@MG_2602 ,

 

Unfortunately we cannot pass infotable parameters to the Database Thing SQL services (see here).  You could iterate the infotable into a string that you could pass into a parameter.  Just make sure that the parameter in your service is surrounded by <<>> instead of [[ ]]. Something like:

 

CALL myProcedure(<<paramString>>);

  

-Nick

Translate
    Rocko
    Rocko19-Tanzanite
    19-Tanzanite
    Sep 12, 2022 8:13 AM UTC

    You can use the toJSON() function on Infotables to serialize them into a string. This is a bit more verbose than if you stringify the infotable yourself, but you have less work recreating the infotable after loading it.

    With postgres, you can also use JSON queries to filter on the JSON fields directly in the query, reducing the amount of returned rows.

    Translate
      jensc17-Peridot
      17-Peridot
      Oct 13, 2022 12:07 PM UTC

      I am not sure if it would be the same on postgreSQL, but on MSSQL you can do:

       

      If you want to avoid using <<>> parameters you could use a JSON string from the infotable as input. Something like this:

       

      let jsonString = InfoTable.toJSON();
      let stringArray = jsonString.rows;

       

      This will give you a string containing an array with all of the rows from the infotable.

       

      You can then pass this into your stored procedure like this:

       

      Here, StringArray is an input parameter with String data type.

       

      exec [StoredProcedure] @json = [[StringArray]]

       

      and some info regarding the stored procedure:

       

      input param is: 

      @jsonNVARCHAR(max)
       
      then you can use the OPENJSON function in MSSQL.

       

      Translate
        Terms & ConditionsAccessibility statement