https://codingsight.com/passing-data-table-as-parameter-to-stored-procedures/
Hi community,
I am trying to implement a feature that will utilize a stored procedure/subroutine that will accept a table as a parameter however i can’t seem to find a way to implement it in the software factory since all subroutine inputs seem to be domains. I found the above topic about this issue but can’t find any followup to the idea.
Sadly the table valued function workaround is not possible here since im trying to implement the procedure in a trigger which would then pass some values from the inserted rows to the procedure. The temp table strategy would work but is not that much cleaner than just putting in a cursor in the trigger looping over the inserted table.
Passing table data to procedures.
Best answer by Mark Jongeling
Hi Anne,
We indeed only support single value domains/types for SQL Server. Joey did open an idea for it to add support for it; feel free to upvote it:
In there, my colleague Anne mentioned another way to achieve it, by creating a temporary table first, then excuting the procedure and using the previously created temporary table inside that procedure.
You can also turn the data from the inserted table into JSON. This JSON data can then be passed onto the procedure through a parameter. In the procedure, you can make a temporary table using the data from the parameter that contains the JSON data (or simply directly select from it).
For example:
declare @json nvarchar(max) -- Or use json datatype with SQL Server 2025
set @json = (select column1, column2, dateadd(day, 1, column3) as column3
-- or select *
from inserted
for json auto);
exec dbo.procedure @json;Hope this helps!
Related links:
- JSON datatype from SQL Server 2025: JSON Data Type - SQL Server | Microsoft Learn
- JSON AUTO (and PATH): Format JSON Output Automatically with AUTO Mode - SQL Server | Microsoft Learn
+23Hi Anne,
We indeed only support single value domains/types for SQL Server. Joey did open an idea for it to add support for it; feel free to upvote it:
In there, my colleague Anne mentioned another way to achieve it, by creating a temporary table first, then excuting the procedure and using the previously created temporary table inside that procedure.
You can also turn the data from the inserted table into JSON. This JSON data can then be passed onto the procedure through a parameter. In the procedure, you can make a temporary table using the data from the parameter that contains the JSON data (or simply directly select from it).
For example:
declare @json nvarchar(max) -- Or use json datatype with SQL Server 2025
set @json = (select column1, column2, dateadd(day, 1, column3) as column3
-- or select *
from inserted
for json auto);
exec dbo.procedure @json;Hope this helps!
Related links:
- JSON datatype from SQL Server 2025: JSON Data Type - SQL Server | Microsoft Learn
- JSON AUTO (and PATH): Format JSON Output Automatically with AUTO Mode - SQL Server | Microsoft Learn
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.