0

Hi Everyone,
I would need your help. I have a project that I need to insert multiple records into my table. Here is more detail.
I design a purchase order table with 3 columns (a, b, c):
a: is primary key with identity (1,1) primary key
b: is defined integer (allow duplicate)
c: is varchar(5)

I want to have multiple records with same value of a.
For ex: when I create purchase order # 1. I have multiple records inside the PO# 1. Also with PO# 2, I have multiple records....also so on.

How can I write a stored procedure for this needed?

2
Contributors
19
Replies
23
Views
9 Years
Discussion Span
Last Post by phuynh77
0

advice would be you don't want multiple records with the same value of a

for one your database is set to not allow it
for two, you need to uniquely identify this record

might want to rethink the schema

something with a purchaseorder
and purchaseorderdetails

purchaseorder
PURCHASE_ORDER_ID
PO_DATE
CLIENT_ID

purchaseorderdetails
PURCHASE_ORDER_DETAILS_ID
PURCHASE_ORDER_ID
ITEM_ID
ITEM_AMOUNT

this will allow you to have multiple records for a single purchase order

0

advice would be you don't want multiple records with the same value of a

for one your database is set to not allow it
for two, you need to uniquely identify this record

might want to rethink the schema

something with a purchaseorder
and purchaseorderdetails

purchaseorder
PURCHASE_ORDER_ID
PO_DATE
CLIENT_ID

purchaseorderdetails
PURCHASE_ORDER_DETAILS_ID
PURCHASE_ORDER_ID
ITEM_ID
ITEM_AMOUNT

this will allow you to have multiple records for a single purchase order

Hi There,
Thank you for your idea. I will update my purchaseorder table and add purchaseorderdetail table into my database. Could you please send me the stored procedure that allows how to have multiple records insert into. Thanks again.

0

you normally wouldn't pass arrays to the stored procedure

for each insert you should make the call to insert purchaseorderdetails, ie 5 items 5 insert calls

passing arrays to a stored procedure i wouldn't consider good practice, the workaround is normally to pass a comma delimited string, but once again i would recommend making the database calls to insert from code side and not rely on it from database side

0

you normally wouldn't pass arrays to the stored procedure

for each insert you should make the call to insert purchaseorderdetails, ie 5 items 5 insert calls

passing arrays to a stored procedure i wouldn't consider good practice, the workaround is normally to pass a comma delimited string, but once again i would recommend making the database calls to insert from code side and not rely on it from database side

===================
Hi dickersonka
I'd need your help again. Per your help, I create 2 tables (PurchaseOrder & PurchaseOrderDetails)
PO table: POID int Identity(1,1) primary key
POInputDate smalldatetime

POD table: PODID int identity(1,1) primary key,
PODID int foreign key,
ItemID int, ItemPrice money
I have coded sp_AddPurchaseOrder with multiple insert (ex: 3 times insert). Somecases, I want to insert only 1 or 2 records and ignore the third insert. But my web application send me a message that Can not insert the records. Could you please share your idea? Your help is appriciated.

Below is my stored procedure:

create proc sp_AddPurchaseOrder
(
@PurchaseOrderInputDate smalldatetime,

@PurchaseOrderID_1 int,
@PurchaseOrderItemID_1 int,
@PurchaseOrderItemPrice_1 money,
@PurchaseOrderQuantity_1 decimal(10,2),

@PurchaseOrderID_2 int,
@PurchaseOrderItemID_2 int,
@PurchaseOrderItemPrice_2 money,
@PurchaseOrderQuantity_2 decimal(10,2),

@PurchaseOrderID_3 int,
@PurchaseOrderItemID_3 int,
@PurchaseOrderItemPrice_3 money,
@PurchaseOrderQuantity_3 decimal(10,2)
)
as 
declare @ident int
insert into purchaseorder values (@PurchaseOrderInputDate)

set @ident = @@IDENTITY

Insert into PurchaseOrderDetails (PurchaseOrderID, PurchaseOrderItemID,
				PurchaseOrderItemPrice, PurchaseOrderQuantity) 
values (@ident, @PurchaseOrderItemID_1, @PurchaseOrderItemPrice_1, @PurchaseOrderQuantity_1)

if @PurchaseOrderID_2 <> ' ' 
begin
	if @PurchaseOrderItemID_2 <> ' '
	begin
		if @PurchaseOrderItemPrice_2 <> ' ' 
		begin
		if @PurchaseOrderQuantity_2 <> ' '
			begin
Insert into PurchaseOrderDetails (PurchaseOrderID, PurchaseOrderItemID,
				PurchaseOrderItemPrice, PurchaseOrderQuantity) 
values (@ident, @PurchaseOrderItemID_2, @PurchaseOrderItemPrice_2, @PurchaseOrderQuantity_2)
				end
			end
		end
	end

if @PurchaseOrderID_3 <> ' ' 
begin
	if @PurchaseOrderItemID_3 <> ' '
	begin
		if @PurchaseOrderItemPrice_3 <> ' ' 
		begin
		if @PurchaseOrderQuantity_3 <> ' '
			begin
Insert into PurchaseOrderDetails (PurchaseOrderID, PurchaseOrderItemID,
				PurchaseOrderItemPrice, PurchaseOrderQuantity) 
values (@ident, @PurchaseOrderItemID_3, @PurchaseOrderItemPrice_3, @PurchaseOrderQuantity_3)
				end
			end
		end
	end
0

Although you aren't passing arrays, you should return the id of the purchase order to your code.

Then call a stored procedure for sp_AddPurchaseOrderDetails for each item you have, 1 item 1 call, 3 items 3 calls

0

dickersonka,
I am new with SQL server 2005 and still new to stored procedure. Could you please help me with the sp coding?

0

sure, you had a good start on the first one
we'll do the purchaseorder

create procedure sp_AddPurchaseOrder
(
   @POInputDate smalldatetime,
   @PurchaseOrderId int OUTPUT
)
as

insert into PurchaseOrder (PO_DATE) values (@POInputDate)

select @PurchaseOrderId = @@IDENTITY

then you will use the same concept for creating purchaseorderdetails, except you will pass in the purchaseorderid as a parameter

(btw i just used the date column, and not client_id like we talked about before)

0

sure, you had a good start on the first one
we'll do the purchaseorder

create procedure sp_AddPurchaseOrder
(
   @POInputDate smalldatetime,
   @PurchaseOrderId int OUTPUT
)
as

insert into PurchaseOrder (PO_DATE) values (@POInputDate)

select @PurchaseOrderId = @@IDENTITY

then you will use the same concept for creating purchaseorderdetails, except you will pass in the purchaseorderid as a parameter

(btw i just used the date column, and not client_id like we talked about before)

==================
Hi dickersonka,
I am very sorry to bother you many. I can't figure how to code the second sp. Could you please point to me the "purchaseorderdetails" ? Thanks much

0

purchaseorderdetails
PURCHASE_ORDER_DETAILS_ID
PURCHASE_ORDER_ID
ITEM_ID
ITEM_AMOUNT

create procedure sp_AddPurchaseOrderDetails
(
   @PurchaseOrderId int,
   @ItemId int,
   @ItemAmount decimal(10,2),
   @PurchaseOrderDetailsId int OUTPUT
)
as

insert into PurchaseOrderDetails (PURCHASE_ORDER_ID, ITEM_ID, ITEM_AMOUNT) values (@PurchaseOrderId, @ItemId, @ItemAmount)

select @PurchaseOrderDetailsId = @@IDENTITY
0

purchaseorderdetails
PURCHASE_ORDER_DETAILS_ID
PURCHASE_ORDER_ID
ITEM_ID
ITEM_AMOUNT

create procedure sp_AddPurchaseOrderDetails
(
   @PurchaseOrderId int,
   @ItemId int,
   @ItemAmount decimal(10,2),
   @PurchaseOrderDetailsId int OUTPUT
)
as

insert into PurchaseOrderDetails (PURCHASE_ORDER_ID, ITEM_ID, ITEM_AMOUNT) values (@PurchaseOrderId, @ItemId, @ItemAmount)

select @PurchaseOrderDetailsId = @@IDENTITY

=============================
Hi There,
I am confused now. Please...please...please help me.
Below is the stored procedure that I have learned from you:

create proc sp_AddPurchaseOrder
(
@PurchaseOrderInputDate smalldatetime,
@PurchaseOrderID int output
)
as
insert into purchaseorder (purchaseorderinputdate)
values(@PurchaseOrderInputDate)
select @PurchaseOrderID = @@IDENTITY

I executed this sp first... then execute the one below:


create proc sp_AddPurchaseOrderDetails
(
@PurchaseOrderID int,
@PurchaseOrderItemID int,
@PurchaseOrderItemPrice money,
@PurchaseOrderQuantity decimal(10,2),
@PurchaseOrderDetailsID int output
)
as
insert into purchaseorderdetails (purchaseorderid, purchaseorderitemid, purchaseorderitemprice, purchaseorderquantity)
values (@PurchaseOrderID, @PurchaseOrderItemID, @PurchaseOrderItemPrice, @PurchaseOrderQuantity)

select @PurchaseOrderDetailsID = @@IDENTITY

=====================

but I got the error message:
Msg 201, Level 16, State 4, Procedure sp_AddPurchaseOrderDetails, Line 0
Procedure or function 'sp_AddPurchaseOrderDetails' expects parameter '@PurchaseOrderID', which was not supplied.

Could you please help again? your help is much appreciated.

0

the problem is that when you execute the first query, it is returning your purchaseorderid, which links the purchaseorderdetails to it

you need to pass this in as a parameter from code side

0

the problem is that when you execute the first query, it is returning your purchaseorderid, which links the purchaseorderdetails to it

you need to pass this in as a parameter from code side

==================
Hi dickersonka
I am using C# to code the front application. Do you have any idea for passing the @IDENTITY or you know any link for these information. I'd need to learn about this.

0

Here's a link

http://blogs.msdn.com/vsdata/archive/2006/08/21/711310.aspx

you can change your stored procedure, by removing the "OUT" parameter, and instead of
select @PurchaseOrderId = @@IDENTITY

you just use return @@IDENTITY

=====

Do you have a link that has information for Web application using c# syntax? The link you have sent was for windows application.
Also, you mean that I don't need to state

select @PurchaseOrderID = @@IDENTITY
I can replace with
return @@IDENTITY

is that you mean?

0

sorry, this way does use the way we had first with
OUTPUT and select @PurchaseOrderID = @@IDENTITY

so change it back to that to follow that example, no more changes now :-)

0

sorry, this way does use the way we had first with
OUTPUT and select @PurchaseOrderID = @@IDENTITY

so change it back to that to follow that example, no more changes now :-)

=====
Hi ,
Per coding, I almost get it, accept this piece:

newId = (int)cmdl.Parameters["@NewUserId"].Value;

based on my Visual studio 2005 (C#). It does not recognise
(int)cmdl.Parameters["@NewUserId"].Value;

do you have any idea?

0

NewUserId will be PurchaseOrderId

make sure you have this in the stored procedure as well, like we said before

@PurchaseOrderId int OUTPUT

also make sure if you modified the code it is cmd not cmdl, unless you changed the name

0

Hi dickersonka,
Awesome for your help. I got it. Thank millions. You are the best.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.