sql - Insert a row in Order table and multiple rows in OrderDetails table via stored procedure -
i have 5 tables named guest, orders, order_details, food, , employees. characteristics follows:
create table guest ( guest_id int primary key identity(1,1), guest_fname nvarchar(50), national_id int, mobile nvarchar(50), nationality_id int constraint c15 foreign key(nationality_id) references nationality(nationality_id) ) create table employees ( emp_id int primary key , emp_fname nvarchar(50), constraint c1 --foreign key(mgr_id) references employees(emp_id), foreign key(super_id) references employees(emp_id), ) go create table food ( food_id int primary key identity(1,1), food_name nvarchar(50), food_price money, food_desc nvarchar(200), cat_id int constraint c5 foreign key(cat_id) references food_categories(cat_id) ) go create table orders ( order_id int primary key identity(1,1), order_date datetime, total float, guest_id int, emp_id int, is_paid bit constraint c6 foreign key(guest_id) references guest(guest_id), foreign key(emp_id) references employees(emp_id) ) go create table order_details ( order_id int, food_id int, price money, qty int constraint c7 primary key(order_id,food_id), foreign key(order_id) references orders(order_id), foreign key(food_id) references food(food_id) ) go there 1-to-m relationship between orders , order_details.
i want insert single row orders , multiple rows order_details via stored procedure. please me!
please give me stored procedure , explain algorithm point me in right direction.
as @thepirat000 points out, can use tvp order details/line items since using sql server 2012. pinal dave has great article on tvps may find helpful. combine tvp scalar parameters order properties.
for example:
-- note: *not* accounting order_id in case - i.e. new order -- order_id identity value. create type [dbo].[order_details_type] table ( food_id int, price money, qty int ) go create procedure [dbo].[usp_insert_order] ( @order_id int output, @order_date datetime = null, @total float, @guest_id int, @emp_id int, @is_paid bit = 0, @details order_details_type readonly ) begin set nocount on; -- todo: wrap inserts dbo.orders , dbo.order_details in -- transaction desired. -- todo: check @order_id not exist in dbo.orders , -- dbo.order_details etc. -- fornow: proceed optimistically. :} if @order_date null set @order_date = getdate(); insert dbo.orders (order_date, total, guest_id, emp_id, is_paid) values (@order_date, @total, @guest_id, @emp_id, @is_paid); set @order_id = scope_identity(); insert dbo.order_details (order_id, food_id, price, qty) select @order_id, food_id, price, qty @details; end go executing such stored procedure pretty straighforward, prepare tvp argument table variable:
/* * order up! */ /* data added quick check select top 1 * dbo.employees; --emp_id emp_fname --1 wendy select top 1 * dbo.guest; --guest_id guest_fname national_id mobile --1 joe 1 619-555-1212 select top 2 * dbo.food; --food_id food_name food_price food_desc --1 onion rings 5.00 beer-battered onion rings. --2 kobe burger 10.00 kobe-beef burger. */ -- prepare order. declare @order_id int; -- on order insert declare @now datetime = getdate(); declare @order_details order_details_type; insert @order_details select 1, 5, 1 union select 2, 10, 2; -- insert order. exec dbo.usp_insert_order @order_id = @order_id output, @order_date=@now, @total=25, @guest_id=1, @emp_id=1, @is_paid=0, @details=@order_details; /* * check order. */ select * dbo.orders order_id = @order_id; --order_id order_date total guest_id emp_id is_paid --1 2014-03-13 21:44:45.400 25 1 1 0 select * dbo.order_details order_id = @order_id; --order_id food_id price qty --1 1 5.00 1 --1 2 10.00 2 another option pre-dates tvps may interest - using xml parameter order details/line items , scalar parameters order properties. related resources , examples abound.
a variation of xml-param approach pass xml order and details/line items; think overkill personally.
Comments
Post a Comment