ParentTable

(

Id uniqueidentifier DEFAULT (newsequentialid()) not null,

Created datetime not null,

Creator uniqueidentifier not null,

Modifier uniqueidentifier null,

Modified datetime null

)

ChildTable

(

Id uniqueidentifier DEFAULT (newsequentialid()) not null,

ParentTable_Id not null,

Created datetime not null,

Creator uniqueidentifier not null,

Modifier uniqueidentifier null,

Modified datetime null
)

create Procedure InsertIntoChildTable
AS
BEGIN
DECLARE @countDset int
DECLARE @todaysdate datetime
DECLARE @UserName uniqueidentifier
DECLARE @ParentTable_Id uniqueidentifier
DECLARE @insertIntoChildTable nvarchar(max)
DECLARE @ChildTableName nvarchar(35)

SET @ChildTableName = ChildTable
SET @countDset = 6
SET @todaysdate = GETDATE()
SET @UserName = 'e86aacf4-9887-e911-9724-4439c492b2a7'

BEGIN TRY
BEGIN TRANSACTION
SET @insertIntoChildTable = 'INSERT INTO ' + @ChildTableName + '
(ParentTable_Id, Created, Creator, Modified, Modifier)
VALUES ( (select max(Id) from ParentTable) , @todaysdate, @UserName ,
NULL, NULL) ' + ' GO ' + @countDset

EXECUTE sp_executesql @insertIntoChildTable,N'@ChildTableName
nvarchar(35), @todaysdate datetime, @UserName uniqueidentifier,
@countDset int', @ChildTableName = @ChildTableName, @todaysdate =
@todaysdate, @UserName = @UserName, @countDset = @countDset

COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT 'Could not insert in the Child table'
ROLLBACK TRANSACTION
RETURN
END CATCH

END