Thanks for your quick response. Yes, the calling stored procedure has to insert rows in another table based on the passed in parameter if that table does not have one row for it.

Even the suggestion you provided won't work because i am doing bulk insert from User Define Table Type (UDTT).

The example i specified here is just a basic example and nothing to do with what i am trying to do. But in mine case the situation is more complex than i specified here. I get the collection of rows in the form of UDTT. But the UDTT data is partial data for the update/insert and other partial data is generated based off of other calculation through procedure or through joins.

So, the only thing, I can think of now is using the Cursor for the UDTT and iterate through one by one row and do the Upsert.

Meaning, call the sp based on the each row data from UDTT and store it in a variable and then do the Upsert.

Does that make sense?