Posted by Habib on 06/30/06 07:14

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

[Back to original message]