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]

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

[Erland Sommarskog]

Habib (hp*******@gmail.com) writes:

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.

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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx