How to get an Identity column value (not committed yet) inside a transaction

Question

Inside a TSQL transaction I have an operation of inserting a record into a MyTable1 and an operation of updating a MyTable2 with a value of Identity column of the MyTable1 record, which is only to be inserted after the transaction is commited.
So, how to get this autogenerated value before it is actually inserted into the table?

Code just to illustrate the question:

CREATE TABLE MyTable1(
MyTable1Id int identity(1,1) primary key,
Field1 varchar(50)
) 

CREATE TABLE MyTable2(
MyTable2Id int identity(1,1) primary key,
MyTable1Id int
)


CREATE PROCEDURE MyProc(
   @MyVal1  [varchar](50)
)
AS
BEGIN TRAN
-- try to insert
INSERT INTO MyTable1(
   Field1
)
SELECT @MyVal1

IF @@ERROR <> 0
BEGIN
  ROLLBACK TRAN
END

-- update MyTable2
DECLARE @TheUnknownIdValue int
UPDATE MyTable2
SET    MyTable1Id = @TheUnknownIdValue -- how to get the value needed here?
WHERE ...

IF @@ERROR <> 0
BEGIN
  ROLLBACK TRAN
END

COMMIT TRAN 

Solution 1

Use SCOPE_IDENTITY() just after the INSERT

--
DECLARE @newId INT 
INSERT INTO MyTable1(
   Field1
)
SELECT @MyVal1
--End of Insert here
SELECT @newId = SCOPE_IDENTITY()
--Rest of the procedure

Solution 2

If you are inserting one record at a time you can use the scope_identity() function:

set @TheUnknownIdValue = scope_identity()

do this just after the insert into MyTable1.

In cases where you want to insert multiple rows, the best way is to use an OUTPUT clause to get all the new identities into a working table.

Solution 3

in SQL Server use SCOPE_IDENTITY() which gives the last generated identity value

UPDATE MyTable2
SET    MyTable1Id = SCOPE_IDENTITY() -- how to get the value needed here
WHERE ...

Should you probably abort the whole thing if the first insert fails? Look into Try/Catch in SQL server, you'll find some good patterns there.