I'm curious about a nested procedures sight. I have a procedure, Proc1 whichs accepts a C# modelled UDT which is defined at schema level.
Proc2 - insert: (called from inside Proc1) identifies records that are in the UDT but not in a table and creates new records.
Proc3 - update: (called from inside Proc1) Would this proc be able to see (have sight) of the newly created records from Proc2? IE, is there a "commit" when proc2 finishes?
Proc4 - delete (call from inside Proc1) deletes all properly identified records.
There are no implicit commits when a procedure finishes. But since all procedures in the same call stack are part of the same transaction, they will inherently be part of the same transaction scope (I ignore the possibility that you have defined one of your procedures to use an autonomous transaction and I assume you aren't explicitly ending a transaction by issuing an explicit commit or rollback). So each procedure will see the uncommitted results of all the code run earlier in the same session.