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