在Sql Server 2008中插入多个数据的存储过程 [英] Store Procudere for Insert Multiple Data In Sql Server 2008

问题描述

你好朋友

我有一个与存储过程有关的查询:

我有2个表，分别是1)OrderMaster 2)OrderProduct

这是两个表的详细信息:
OrderMaser:-

Hello Friends

i have one query related to store procedure :

i have 2 table named 1) OrderMaster 2) OrderProduct

here is detail of both table :
OrderMaser :-

ID	int	Unchecked
OrderID	int	Unchecked
OrderDate	datetime	Checked
StoreID	int	Unchecked
CheckoutType	int	Checked
ShippingAddress	varchar(1000)	Unchecked
ZoneID	int	Unchecked
StateID	int	Unchecked
CityID	int	Unchecked
PinCode	int	Unchecked
ContactNo	int	Checked
EmailID	varchar(100)	Checked
OrderStatus	varchar(50)	Checked
Comments	varchar(1000)	Checked
IsDeleted	bit	Unchecked

2)OrderProduct:-

2)OrderProduct :-

ID	int	Unchecked
OrderID	int	Checked
ProductName	varchar(500)	Checked
Quantity	int	Checked
Price	float	Checked

现在我想在创建订单数据时将其插入OrderMaster表中.还添加OrderProduct表，但是该表中有多个产品.在OrderMaster表中有OrderID具有唯一键，在OrderProduct表中有Forienkey.

我该如何解决.... !!!

预先感谢所有人，

Now i want to when i create a order data will insert in OrderMaster table. Also add the OrderProduct table but there is multiple product in that table. there is OrderID have unique key in OrderMaster Table and Forienkey in OrderProduct Table.

how can i solve that ....!!!

Thank To All in Advance,

推荐答案

您可以将数据表传递给存储过程.然后，您可以一次添加任意多的行.

这是一篇介绍如何操作的文章:
将数组或DataTable传递到存储过程中 [ ^ ]

You can pass a datatable to your stored procedure. Then you can add as many rows as you like in one go.

Here''s an article explaining how :
Passing an array or DataTable into a stored procedure[^]

我想您在创建订单时也会传递ProductName，Quantity和PRICE.

因此，对于所有列，要有一个带有输入参数的存储过程


@OrderID
@OrderDate
@StoreID
@CheckoutType
@ShippingAddress
@ZoneID
@StateID
@CityID
@PinCode
@ContactNo
@EmailID
@OrderStatus
@评论
@IsDeleted
@ProductName
@Quantity
@Price

然后插入orderMaster

就像

I guess you will be passing ProductName, Quantity and PRICE also when creating an order.

So have a stored proc with input parameters for all the columns


@OrderID
@OrderDate
@StoreID
@CheckoutType
@ShippingAddress
@ZoneID
@StateID
@CityID
@PinCode
@ContactNo
@EmailID
@OrderStatus
@Comments
@IsDeleted
@ProductName
@Quantity
@Price

Then insert into orderMaster

like

insert into ordermaster values (
@OrderID,	
@OrderDate,
@StoreID,
@CheckoutType,
@ShippingAddress,
@ZoneID,
@StateID,	
@CityID,
@PinCode,
@ContactNo,
@EmailID,
@OrderStatus,
@Comments,
@IsDeleted)

然后插入订单产品

then insert into orderproduct

insert into orderproduct values (@orderid,@ProductName,@Quantity,@Price	)

这不是完整的存储过程.我已经解释了插入的逻辑.你必须从这里写.

This is not complete stored proc. I have explained the logic to insert. You have to write from this.

这篇关于在Sql Server 2008中插入多个数据的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！