SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SampleTransactionalProc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SampleTransactionalProc]
GO

/* =============================================================
** Procedure: SampleTransactionalProc
** Purpose: Use this procedure as a template for creating a 
**   transactional stored procedure.
** Parameters:
** - @Parameter1: Describe parameters here or remove this 
**   section when there are no parameters.
** Return:
** - ReturnCode: Describe the meaning of the return code here
**   if you return specific values.
** History:
** - JHB 04/21/05: Create sample transactional procedure.
** ============================================================= */
CREATE PROCEDURE [dbo].[SampleTransactionalProc]
	@Parameter1 int = 0
AS BEGIN
	DECLARE @ReturnCode int,
		@ErrorCode int,
		@RowCount int,
		@InTrans tinyint,
		@Message varchar(200)
	SELECT	@ReturnCode = 0,
		@ErrorCode = 0,
		@RowCount = 0,
		@InTrans = 0,
		@Message = 'SampleTransactionalProc: '
	
	SET NOCOUNT ON
	
	/* Verify that required parameters were provided. */
	IF @Parameter1 < 1 BEGIN
		SET @ReturnCode = 1
		SET @Message = @Message + 'Parameter1 must be greater than zero.'
		GOTO ProcError
	END

	/* Begin a transaction to wrap operations that must be completed together */
	SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
	BEGIN TRANSACTION
	SET @InTrans = 1
	
	/* Perform first operation. */
	-- INSERT/UPDATE/DELETE goes here
	SELECT @ErrorCode = @@ERROR, @RowCount = @@ROWCOUNT
	IF @ErrorCode <> 0 OR @RowCount = 0 BEGIN
		SET @ReturnCode = 101
		SET @Message = @Message + 'Unable to perform first operation.'
		GOTO ProcError
	END
	
	/* Perform second operation. */
	-- INSERT/UPDATE/DELETE goes here
	SELECT @ErrorCode = @@ERROR, @RowCount = @@ROWCOUNT
	IF @ErrorCode <> 0 OR @RowCount = 0 BEGIN
		SET @ReturnCode = 102
		SET @Message = @Message + 'Unable to perform second operation.'
		GOTO ProcError
	END
	
	/* If you have not found an error, normal control flow drops through here. */
	
ProcExit:
	/* Commit or abort the transaction. */
	IF @InTrans = 1 BEGIN
		IF @ReturnCode = 0 BEGIN
			COMMIT TRANSACTION
		END ELSE BEGIN
			ROLLBACK TRANSACTION
		END
	END
	SET TRANSACTION ISOLATION LEVEL READ COMMITTED
	RETURN(@ReturnCode)
ProcError:
	RAISERROR(@Message,16,1)
	GOTO ProcExit
END
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO