Skip to main content

https://codingsight.com/passing-data-table-as-parameter-to-stored-procedures/

To pass large amounts of data to a performance sensitive stored procedures we would like to pass a table as a parameter to a stored procedure as described in the article above.

For this we need to define a type/domain as a table, and we need to set the parameter of a stored procedure to read only. Those are both options currently not available to us in the SF.

Could this be made possible please?

We are currently using the 2020.2 version of the SF.

 

Hi Joey,

We currently don't support User-defined Table types. 

Alternatively you could make a Subroutine of the type Table, which results in a Table-valued function. That works quite similar to a User-defined Table type as far as I can read. This Table-valued function can then be used inside procedures and queries. Would that be a good alternative?

Example of Table-valued function

 

Unfortunately that won't work for us. We're looking to pass tables as parameter to procedures, in order to  send larger sets of data to a procedure.

Check the link I originally added to the topic, it explains it quite extensively.

Feel free to create an Idea for this :smile: I do think this would be a great addition to the platform.

How about using a staging table or temp table for this? You can upload the data you need to this table. Then get the needed data within the stored procedure from the table. If needed you could even add indexes to the tables. 

We already use staging/temp tables for similar needs… However because of performance and security issues this is not a desired solution. I'll add the idea, as Mark suggested.

Reply


Terms & ConditionsCookie settings