How to pass value to the data-table in the stored procedure while executing in SQL Server 2008

Question

I have a stored procedure which takes 2 parameters of UDT.

How can I pass the value to the stored proc parameters while executing this stored procedure?

For ex:

create  procedure temp
(
   @input1 AS Datatable1 READONLY, 
   @input2 AS Datateble2 READONLY,
)

Here Datatable1 and Datatable2 are table types

So how can I execute the procedure?

Can anyone guide me?

See: http://www.sqlteam.com/article/sql-server-2008-table-valued-parameters — marc_s, Mar 11 '15 at 05:28
Check Panagiotis Kanavos's answer in http://stackoverflow.com/questions/28541079/pass-list-of-strings-to-a-stored-procedure/28541212#28541212 — Saravana Kumar, Mar 11 '15 at 05:41
check answer http://stackoverflow.com/questions/23911789/passing-parameter-of-list-of-values-and-tables-to-stored-procedure/23931148#23931148 — Hiral Nayak, Mar 11 '15 at 05:50

score 2 · Answer 1 · edited Apr 26 '16 at 13:22

2

DECLARE @p1 DataTable1;
DECLARE @p2 DataTable2;

INSERT @p1 VALUES (...);
INSERT @p2 VALUES (...);

EXEC temp @input1= @p1, @input2= @p2;

edited Apr 26 '16 at 13:22

Mac D'zen

answered Mar 11 '15 at 05:48

Dan Field

If this doesn't answer your question, could you clarify why? You're asking how to pass table valued parameters and execute a stored procedure - this is how you do it. Without column information on the table types I can't provide any more details about what the insert should look like. – Dan Field Mar 11 '15 at 05:56
No problem. If it answered your question, please consider accepting it as the answer. I still don't know why it got voted down, this is really the easiest and most straightforward way to use TVPs in SQL.... – Dan Field Mar 24 '15 at 12:36

1 Answers1