inserting into two tables and transaction problem

[Habib]

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.


Thanks

Habib

[Joe Weinstein]

Re: inserting into two tables and transaction problem



Habib wrote:
[color=blue]
> 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.[/color]

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.CustN ame
column, and then try to run the procedure twice with the same
customer name.

Joe Weinstein at BEA Systems[color=blue]
>
>
> Thanks
>
> Habib
>[/color]

[Erland Sommarskog]

Re: inserting into two tables and transaction problem

Habib (hpourfard@gmai l.com) writes:[color=blue]
> 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.[/color]

Error handling in SQL 2000 is a difficult topic. When you get a compilation
error during run-time (which is possible in SQL Server), the procedure
is aborted on the spot, but the transaction is not rolled back. Thus,
the caller needs to clear up the mess.

In SQL 2005 you have TRY-CATCH which makes error handling a little easier,
but a CATCH handler in this procedure would not have helped, but a
CATCH handler in the caller would catch the error.

For a longer discussion on error handling, you may be interested in this
article of mine: http://www.sommarskog.se/error-handling-I.html.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx