Skip to main content
Solved

Passing Data table as Parameter to Stored Procedures

  • November 4, 2021
  • 5 replies
  • 132 views

Forum|alt.badge.img

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.

 

Best answer by Joey van Gentevoort

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.

View original
Did this topic help you find an answer to your question?
This topic has been closed for comments

5 replies

Mark Jongeling
Administrator
Forum|alt.badge.img+23

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

 


Forum|alt.badge.img

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.


Mark Jongeling
Administrator
Forum|alt.badge.img+23

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


Forum|alt.badge.img+17
  • Moderator
  • 761 replies
  • November 9, 2021

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. 


Forum|alt.badge.img

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.


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings