sql - Stored procedure to insert a list of different types of items in to a table -

i have following 2 tables

create table orders ( orderid int identity not null, staffid int not null, totalprice money not null, orderdatetime datetime not null  primary key (orderid), foreign key (staffid) references staff(staffid) )   create table orderdetails ( orderdetailid int identity not null, orderid int not null, itemid int, extrasid int, itemquantity int, extrasquantity int  primary key (orderdetailid) foreign key (orderid) references orders(orderid), foreign key (extrasid) references extras(extrasid), foreign key (itemid) references item(itemid) ) 

i create stored procedure create new order inserting data in both tables. procedure should take parameters staffid, totalprice , bought products. products can many items, many extras or both.

is there way can have list of itemid's , list of extrasid's parameters inserted in orderdetails table correctly?

create type first

create type dbo.ty_product_orders table (  itemid         int ,extrasid       int ,itemquantity   int ,extrasquantity int ) go 

procedure definition

now make procedure accept parameter of type.

create procedure usp_place_order  @staffid        int ,@totalprice     money ,@order          dbo.ty_product_orders readonly ,@orderplaced    bit = 0 output begin   set nocount on;   -- table variable hold identity values    declare @orderdetails table    (      orderid        int,      itemid         int     ,extrasid       int     ,itemquantity   int     ,extrasquantity int   )  begin try  begin transaction;  -- insert orders  insert orders (staffid , totalprice , orderdatetime) output inserted.orderid , inserted.itemid ,inserted.extrasid ,        inserted.itemquantity ,inserted.extrasquantity           @orderdetails(orderid , itemid , extrasid                              ,itemquantity ,extrasquantity) select @staffid , @totalprice , getdate() @order   -- insert orderdetails  insert orderdetails ((orderid , itemid , extrasid  ,itemquantity ,extrasquantity) select orderid , itemid , extrasid,itemquantity ,extrasquantity @orderdetails    commit transaction; end try  begin catch  if (@@trancount <> 0)     rollback transaction;    -- other error logging here   end catch  end