MS-SQL stored procedure to insert into one-to-many-to-many relationship

Question

I have three tables to represent a single record in a one-to-many-to-many relationship. I would like to create a stored procedure in MS SQL Server to create the record all at once. I have seen examples on how to do this with just two tables with a table-valued parameter to populate the child table values, but can't find how to do the same with one more table deep.

• Table 1 - request(req_id, requester, date)
• Table 2 - reqitems(line_id, req_id, descript, qty)
• Table 2 - itemcharge(charge_id, line_id, dept, percent)

So a request can have 1 or more items & each item can have 1 or more charges, adding up to 100%.

I appreciate any help you have. Thanks

Example to populate Tables 1 & 2

CREATE TYPE dbo.reqlineTable AS TABLE
(
    descript nvarchar(100),
    qty int
);
GO

CREATE PROCEDURE InsertRequest 
    @requester nvarchar(50), 
    @date datetime,
    @lines reqlineTable READONLY
AS
BEGIN

    SET NOCOUNT ON;

    DECLARE @req_id int;

    INSERT INTO dbo.request
           (requestor
           ,date)
     VALUES
           (@requester
           ,@date);

    SET @req_id = SCOPE_IDENTITY();

    INSERT INTO dbo.reqitems
    SELECT qty
        , descript
        , @req_id
    FROM @lines;

    SET NOCOUNT OFF;

END
GO

Answer 1

You can use the OUTPUT clause of the INSERT to capture the identity values that were generated like so:

DECLARE @output TABLE (
   line_id int,
   qty int,
   descript nvarchar(100),
   req_id int
);

INSERT INTO dbo.reqitems
OUTPUT INSERTED.line_id, INSERTED.qty, INSERTED.descript, INSERTED.req_id
  INTO @output
SELECT qty
    , descript
    , @req_id
FROM @lines;

Then you can use the @output table variable to perform the insert into your third table:

INSERT itemcharge
SELECT /* whatever values you need go here */
  FROM @output;

OUTPUT Clause (Transact-SQL)

Answer 2

Maybe something like this:

SELECT r.line_id, r.descript 
FROM reqitems as r 
INNER JOIN @lines as l
ON r.descript = l.descript
WHERE r.req_id = @req_id;

Then you link it to other values to insert into itemcharge
Only the way to link I see as by descript.