Table-Valued Parameter without using a stored procedure

Question

It seems all I can find online are examples of table-valued parameters that require using a stored procedure. I recall doing this in the past without the stored procedure. Is that possible?

This code keeps throwing an error about the type not being specified.

SqlCommand cmd = new SqlCommand(@"
        UPDATE t1
        SET t1.ScheduledStartUTC = t2.ScheduledStartUTC
        FROM ScheduleTickets AS t1
            INNER JOIN @SetScheduledStart AS t2 ON t1.ScheduleId = t2.ScheduleId AND t1.PatchSessionId  = t2.PatchSessionId 
    ", c);
cmd.Parameters.Add("@SetScheduledStart", SqlDbType.Structured).Value = SetScheduleTicketsDateDT;
cmd.ExecuteNonQuery();

That's because you're also required to set the `TypeName` property if you're using `SqlDbType.Structured` for TVPs. (And you cannot get around creating this type first, if that's what you're going for.) — Jeroen Mostert, Mar 19 '18 at 14:56
Ok, I found how to specify the TypeName: cmd.Parameters["@SetScheduledStart"].TypeName = "dbo.tvpUpdateScheduledStart"; — Connie DeCinko CS, Mar 19 '18 at 15:43

score 1 · Answer 1 · answered Jun 12 '20 at 13:27

Here you can find how to use it without stored procedure: Passing a Table-Valued Parameter to a Parameterized SQL Statement

Basically, it requires you to:

CREATE TYPE dbo.tvpUpdateScheduledStart AS TABLE (ScheduleId int, PatchSessionId int) on the server beforehand.
Specify this type in TypeName property of a SqlParameter.

Table-Valued Parameter without using a stored procedure

1 Answers1

Linked