Store Procudere for Insert Multiple Data In Sql Server 2008

Question

0.00/5 (No votes)

See more:

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 :-

SQL

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 :-

SQL

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

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,

Posted 16-Aug-12 21:09pm

Yatin chauhan

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

StianSandberg · Answer 1 · 2012-08-16T21:25:00

Solution 2

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[^]

Posted 16-Aug-12 21:25pm

StianSandberg

Santhosh Kumar Jayaraman · Answer 2 · 2012-08-16T21:20:00

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

SQL

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

then insert into orderproduct

SQL

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.