Posted by Erland Sommarskog on 06/30/06 22:24

Habib (hpourfard@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, esquel@sommarskog.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

[Back to original message]