October 5, 2006 at 10:04 pm
Hi everyone,
I am just wodering in asp program, if there is anybody writing store procedure for inserting data into database since there are so many parameters need to be passed into store procedure(assume there are many columns in the table).
I need to insert data into two separate tables, the relation between these two tables is 1 row of data in table1 could have multiple rows in table2 related to table1, but if the data insertion into any one of the tables is failed, the transaction will roll back and no data will be remained in any of the tables.
If this needs to be handled in asp program using transact sql, how do I implement it. If I need to it using store procedure, how do I pass the error or success signal to asp program from database.
Thank you.
--
Betty
October 6, 2006 at 4:03 pm
You can declare @Error as output and do something like that:
DECLARE @Error INT -- Output in procedure
SET @Error = 0
Begin Transaction
INSERT Table1 ...
Values ...
SET @Error = @@Error
If @Error <> 0
begin
GOTO EndProc
end
INSERT Table2 ...
Values ...
SET @Error = @@Error
If @Error <> 0
begin
GOTO EndProc
end
EndProc:
IF @Error<>0
ROLLBACK
ELSE
COMMIT
October 16, 2006 at 10:57 am
Hi Roi,
Thanks for your idea. I probably have to utilize in both way because I probably have more than 10 rows in table2 for each row in table1. Since I cannot pass so many parameters in one store procedure call I probably have to seperate those two insert statements into two store procedure, if the store procedure call for inserting data into table2 failed, I just need to delete the record in table1 and rollback. But I have to pass the primary key from table1 to this store procedure call and will have 10 store procedure calls, that sound quite abnormal.
I am wodering if there is a better way like array or table parameter.
Betty
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply