Posted by Joe Weinstein on 06/30/06 19:03

Habib wrote:

> CREATE PROCEDURE SimpleInsert (
> @custname custname_type,
> @carname carname_type)
>
> AS
>
> BEGIN TRANSACTION
>
>
> INSERT Customers (CustName) VALUES (@custname)
> IF @@error <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
>
> DECLARE @custid int
> SELECT @custid = scope_identity()
>
>
> INSERT Cars (ID, CarName) VALUES (@custid, @carname)
> IF @@error <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
>
> COMMIT TRANSACTION
>
>
> IF no error this works but to test transaction, I chanded the table
> name of second insert to 'car' in which doesn't exist and this error
> occured (calling sp by ASP .NET page):
>
>
> Invalid object name 'car'. Transaction count after EXECUTE indicates
> that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous
> count = 0, current count = 1.

Recompile the procedure. If you rename the table the procedure's
stored query plan is invalid. You have found a type of error for
which a procedure will not continue. If you simply want to test
the transaction, make a unique index on the Customers.CustName
column, and then try to run the procedure twice with the same
customer name.

Joe Weinstein at BEA Systems
>
>
> Thanks
>
> Habib
>

• Next in forum: OR in the WHERE Clause?
• Prev in forum: Re: Update statement, then insert what wasn't available to be updated.
• Thread view: Re: inserting into two tables and transaction problem

[Reply to this message]