Stored procedure handling 1 parent record and multiple child recor

G

Guest

I am writing a stored procedure to handle Order and OrderDetail tables. I
created a tempTable physically on the database, and insert the OrderDetails
into the tempTable first, then I try to use the tempTable in a select
statement to insert the all the records to the OrderDetails. If there is any
record failed, rollback all the transaction. But when I run the program, it
generates error message saying that the column name of number of supplied
items does not match table definition.

I have checked the table. It has exactly the same number of columns. The
only difference is the tempTable has no POID, which is an auto number
generated after the Order table has inserted. I use the @POID as the
parameter to pass in the value into the OrderDetail with other columns from
tempTable.

I don't know what goes wrong. Please help me!

Thanks

Anita



create proc trans
@OrderDate smalldatetime,
@shipDate smalldatetime,
@CustID int,
@SupID int,
@FavUnfav real,
@ShipTo char(35),
@ShipAddress char(50),
@ShipCity char(15),
@ShipProv char(10),
@ShipPC char(10),
@ShipPhone char(10),
@AccntPO char(20),
@POID int OUTPUT

as
begin transaction
insert into tblPO
values(@OrderDate,@shipDate,@CustID,@SupID,@FavUnfav,@ShipTo,
@ShipAddress,@ShipCity,@ShipProv,@ShipPC,@ShipPhone,@AccntPO) set @POID
=@@identity

declare @poDetailInsert varchar(500)
et @poDetailInsert = 'insert into tblPODetail(POID, productID, qtyorder,
qtyship, price, CommissionRate) select '
+ convert(varchar,@POID) + 'ProductID, qtyorder, qtyship, price,
CommissionRate from tempPODetail'

IF @@TRANCOUNT <> 0
BEGIN
PRINT 'A transaction needs to be rolled back'
ROLLBACK TRAN
END
else
commit transaction

delete from tempPODetail
exec(@poDetailInsert)

go
 
J

Jeff Jarrell

the simple way to avoid this is to ALWAYs use column lists...

insert into SOMETABLE (col1,col2) <----
select @col1,@col2
 
G

Guest

Hi Jeff,
Thanks very much for replying my question. I have used the column lists as
you advice. But it still generates the same error message. Please help. Here
is the code:

create proc trans
@OrderDate smalldatetime,
@shipDate smalldatetime,
@CustID int,
@SupID int,
@FavUnfav real,
@ShipTo char(35),
@ShipAddress char(50),
@ShipCity char(15),
@ShipProv char(10),
@ShipPC char(10),
@ShipPhone char(10),
@AccntPO char(20),
@POID int OUTPUT

as
begin transaction
insert into tblPO
values(@OrderDate,@shipDate,@CustID,@SupID,@FavUnfav,@ShipTo,
@ShipAddress,@ShipCity,@ShipProv,@ShipPC,@ShipPhone,@AccntPO)
set @POID =@@identity

declare @poDetailInsert varchar(500)
set @poDetailInsert = 'insert into tblPODetail(POID, productID, qtyorder,
qtyship, price, CommissionRate) select '
-- + convert(varchar,@POID) + 'ProductID, qtyorder, qtyship, price,
CommissionRate from tempPODetail'

IF @@TRANCOUNT <> 0
BEGIN
PRINT 'A transaction needs to be rolled back'
ROLLBACK TRAN
END
else
commit transaction

delete from tempPODetail
exec(@poDetailInsert)

go
 
Top