SQL- Stored procedure: Skip first two INSERT statements

I encounter an interesting question as follows:

You have a stored procedure that needs to run three INSERT statements, the first two of which should all complete or all not complete. The third INSERT should run no matter what, succeeding/failing independently. How do you code this?

---

Say you have the following simple table:

CREATE TABLE Examples.ThreeInsert ( ThreeInsertID int CONSTRAINT PKThreeInsert PRIMARY KEY)

You can code something like the following.

In code destined for production work, you likely want to code nested TRY...CATCH blocks, and save the error messages from the first two INSERT statements in variables to throw at the end.

CREATE PROCEDURE Examples.ThreeInsert_Create @SecondValue int = 2 -- Define a parameter as int to pass values, int ranges -2,147,483,648 to +2,147,483,648 AS Set NOCOUNT ON BEGIN TRY BEGIN TRANSACTION; INSERT INTO Examples.ThreeInsert(ThreeInsertID) VALUES(1) INSERT INTO Examples.ThreeInsert(ThreeInsertID) VALUES(@SecondValue) COMMIT TRANSACTION; END TRY BEGEIN CATCH IF XACT_STATE() <> 0 -- if XACT_STATE() = 0, then it means that there is no active user transaction for the current result. ROLLBACK TRANSACTION; --Here no throw, it will mean no reporting of message END CATCH; INSERT INTO Examples.ThreeInsert(ThreeInsertID) VALUES(3);