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