SQL Transaction

USE [MyDatabase]
GO
/****** Object:  StoredProcedure [dbo].[ExampleTransaction]    Script Date: 08/18/2009 10:02:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		me
-- Create date:   08/16/2009 10:02:31 PST
-- Description:	...
--		 		...
-- =============================================
ALTER PROCEDURE [dbo].[ExampleTransaction]
--(
-- -- ...
--)
AS
	/* SET NOCOUNT ON */

	DECLARE @pkeyID INT
	DECLARE @error INT
	SET @pkeyID = 0
	SET @error = 0

	BEGIN TRANSACTION 
	
		INSERT INTO MyTable (...) VALUES (...)

	SET @error=@@ERROR
	IF @error <> 0
	BEGIN
		ROLLBACK TRANSACTION
		SET @pkeyID = -1
	END
	ELSE
	BEGIN
		COMMIT TRANSACTION
		SET @pkeyID=@@IDENTITY

	END

	RETURN @pkeyID
Advertisements