how to iterate sql TVP values in loop for multiple insert stored procedure

Question

I am in a situation where I need to insert multiple records in one stored procedure.

I use "Table valued parameter", comes from c# code and passing it to stored procedure. (this TVP has a list of analise IDs)

So, I am trying to create a loop; to insert multiple records and to iterate until the max rowsize of the @TVP rows.

How can i get the row size of the TVP (Table Valued Parameters) passed in the SP and iterate in these TVP rows to take their tvp.id's one by one, for a multiple insert?

SP is like this:

create proc insertTable
(
   @nID int,
   @TVP Analises READONLY
)
as
declare @i int
BEGIN
   While @i <--max rownumber of @TVP
   BEGIN
     --insert into tbl_insertAnalyses
     --values(@nID,@tvp.analiseID[i]) -- >need to iterate here
   END
END

Thanks.

try using something like: `INSERT tbl_insertAnalyses (col1, col2) SELECT col1, col2 FROM @TVP ` and forget your loop — KM., May 21 '15 at 14:01

score 2 · Accepted Answer · answered May 21 '15 at 14:00

A Table-Valued Paramater (TVP) is a table variable. You can just do a simple INSERT...SELECT:

CREATE PROCEDURE insertTable
(
   @nID int,
   @TVP Analises READONLY
)
AS
SET NOCOUNT ON;

INSERT INTO tbl_insertAnalyses (ID, AnalysisID)
   SELECT @nID, t.analiseID
   FROM   @TVP t;

how to iterate sql TVP values in loop for multiple insert stored procedure

1 Answers1