CREATE PROCEDURE dbo.MyProc
(
@EmployeeID INT
)
AS
BEGIN
DECLARE @ContractID INT;
DECLARE @Table1Row TABLE
(
ContractID INT
);
BEGIN TRAN;
BEGIN TRY
INSERT INTO dbo.Table1 (EmployeeID, DateCreated)
OUTPUT INSERTED.ContractID INTO @Table1Row
VALUES (@EmployeeID, GETDATE());
SELECT @ContractID = t1.ContractID
FROM @Table1Row t1;
INSERT INTO dbo.Table2 (ContractID, EmployeeID)
VALUES (@ContractID, @EmployeeID);
COMMIT TRAN;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRAN;