Remove From My Forums

Missing Begin Tran, not missing? Newbie Question :o)

Question

Hi All

I have the following stored procedure, that runs OK when no errors are encountered but I get the following error when it tries to handle an error:

Msg 3903, Level 16, State 1, Procedure usp_dataimport, Line 287
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

Please can someone advise where I'm going wrong? apologies it is a newbie type question but we've all got to start somewhere :)

Thanks

Dominic

USE [Staff_Data]
GO
/****** Object:  StoredProcedure [dbo].[usp_dataimport]    Script Date: 09/15/2011 12:32:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO







-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[usp_dataimport]
	-- Add the parameters for the stored procedure here
	@PayPeriod int,
	@RetCode int = NULL OUTPUT,
	@RetMsg varchar(100) = NULL OUTPUT

AS

DECLARE @myERROR int -- Local @@ERROR
DECLARE @checkPeriod int 

BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
SELECT @CheckPeriod = dbo.[PAY_CURRENTPERIOD].MaxOfPeriod FROM dbo.[PAY_CURRENTPERIOD]

IF @CheckPeriod+1 <> @PayPeriod
BEGIN
    SELECT 
    @RetCode = 0,
    @RetMsg = 'Wrong Period'
GOTO HANDLE_ERROR
END

BEGIN TRAN 

    -- Insert statements for procedure here
	CREATE TABLE	[dbo].[PAY_GBPTEMP](
					[Fiscal Period] [varchar](50),
					[Fiscal Year] [smallint],
					[Fiscal Period Number] [smallint],
					[Ledger Short Name] [varchar](50),
					[Entity Code] [varchar](50),
					[Cost Centre Code] [varchar](50),
					[Subjective Code] [varchar](50),
					[Analysis One Code] [varchar](50),
					[Analysis Two Code] [varchar](50),
					[Spare] [varchar](50),
					[Payroll] [varchar](50),
					[Pay Period] [varchar](50),
					[Employee Number] [varchar](50),
					[Employee Name] [varchar](50),
					[Post Number] [varchar](50),
					[Payscal] [varchar](50),
					[Increment point] [varchar](50),
					[Increment date] [int],
					[Element Group] [varchar](50),
					[Element Type description] [varchar](50),
					[Pay Element Number] [varchar](50),
					[Pay Element Description] [varchar](50),
					[Currency] [varchar](50),
					[Pay Amount] [float],
					[Previous Month Payment] [float])
					
BULK INSERT [dbo].[PAY_GBPTEMP] FROM      
'\\nhfps08\bnhfinance$\_GenLed Source\NHS_GL_Payroll_Analysis_Extrac_120911 PAY GBP.txt'  
WITH (FIRSTROW = 2, FIELDTERMINATOR = '|', ROWTERMINATOR = '\n') 

SELECT @myERROR = @@ERROR
IF @myERROR != 0 
BEGIN
SELECT 
    @RetCode = 0,
    @RetMsg = 'Error Encountered'
GOTO HANDLE_ERROR
END

   -- Insert statements for procedure here
	CREATE TABLE	[dbo].[PAY_WTETEMP](
					[Fiscal Period] [varchar](50),
					[Fiscal Year] [smallint],
					[Fiscal Period Number] [smallint],
					[Ledger Short Name] [varchar](50),
					[Entity Code] [varchar](50),
					[Cost Centre Code] [varchar](50),
					[Subjective Code] [varchar](50),
					[Analysis One Code] [varchar](50),
					[Analysis Two Code] [varchar](50),
					[Spare] [varchar](50),
					[Payroll] [varchar](50),
					[Pay Period] [varchar](50),
					[Employee Number] [varchar](50),
					[Employee Name] [varchar](50),
					[Post Number] [varchar](50),
					[Payscal] [varchar](50),
					[Increment point] [varchar](50),
					[Increment date] [int],
					[Element Group] [varchar](50),
					[Element Type description] [varchar](50),
					[Pay Element Number] [varchar](50),
					[Pay Element Description] [varchar](50),
					[Currency] [varchar](50),
					[WTE] [float],
					[Contract WTE] [float],
					[Paid WTE] [float],
					[Worked WTE] [float],
					[Headcount] [float])
					
BULK INSERT [dbo].[PAY_WTETEMP] FROM      
'\\nhfps08\bnhfinance$\_GenLed Source\NHS_GL_Payroll_WTE_Analysis_Ex_120911 WTE STAT.txt'  
WITH (FIRSTROW = 2, FIELDTERMINATOR = '|', ROWTERMINATOR = '\n') 

SELECT @myERROR = @@ERROR
IF @myERROR != 0 
BEGIN
SELECT 
    @RetCode = 0,
    @RetMsg = 'Error Encountered'
GOTO HANDLE_ERROR
END

DELETE FROM [Staff_Data].[dbo].[PAY_DETAIL]

	INSERT INTO [dbo].[PAY_DETAIL](
	   [KEY]	
	  ,[Fiscal Period]
      ,[Fiscal Year]
      ,[Fiscal Period Number]
      ,[Ledger Short Name]
      ,[Entity Code]
      ,[Cost Centre Code]
      ,[Subjective Code]
      ,[Analysis One Code]
      ,[Analysis Two Code]
      ,[Spare]
      ,[Payroll]
      ,[Pay Period]
      ,[Employee Number]
      ,[Employee Name]
      ,[Post Number]
      ,[Payscal]
      ,[Increment point]
      ,[Increment date]
      ,[Element Group]
      ,[Element Type description]
      ,[Pay Element Number]
      ,[Pay Element Description]
      ,[Currency]
      ,[Pay Amount]
	  ,[WTE]
	  ,[Contract WTE]
	  ,[Paid WTE]
	  ,[Worked WTE]
	  ,[Headcount])
	SELECT  
	   [Employee Number]+'|'+[Cost Centre Code]+'|'+[Subjective Code]+'|'+[Analysis One Code]
	  ,[Fiscal Period]
      ,[Fiscal Year]
      ,[Fiscal Period Number]
      ,[Ledger Short Name]
      ,[Entity Code]
      ,[Cost Centre Code]
      ,[Subjective Code]
      ,[Analysis One Code]
      ,[Analysis Two Code]
      ,[Spare]
      ,[Payroll]
      ,[Pay Period]
      ,[Employee Number]
      ,[Employee Name]
      ,[Post Number]
      ,[Payscal]
      ,[Increment point]
      ,dateadd(DAY,-2,[Increment date])
      ,[Element Group]
      ,[Element Type description]
      ,[Pay Element Number]
      ,[Pay Element Description]
      ,[Currency]
      ,[Pay Amount]
      ,0 AS WTE 
      ,0 AS [Contract WTE]
      ,0 AS [Paid WTE] 
      ,0 AS [Worked WTE]
      ,0 AS Headcount
FROM [dbo].[PAY_GBPTEMP]

DROP TABLE [dbo].[PAY_GBPTEMP]

SELECT @myERROR = @@ERROR
IF @myERROR != 0 
BEGIN
SELECT 
    @RetCode = 0,
    @RetMsg = 'Error Encountered'
GOTO HANDLE_ERROR
END

	INSERT INTO [dbo].[PAY_DETAIL](
	   [KEY]	
	  ,[Fiscal Period]
      ,[Fiscal Year]
      ,[Fiscal Period Number]
      ,[Ledger Short Name]
      ,[Entity Code]
      ,[Cost Centre Code]
      ,[Subjective Code]
      ,[Analysis One Code]
      ,[Analysis Two Code]
      ,[Spare]
      ,[Payroll]
      ,[Pay Period]
      ,[Employee Number]
      ,[Employee Name]
      ,[Post Number]
      ,[Payscal]
      ,[Increment point]
      ,[Increment date]
      ,[Element Group]
      ,[Element Type description]
      ,[Pay Element Number]
      ,[Pay Element Description]
      ,[Currency]
      ,[Pay Amount]
	  ,[WTE]
	  ,[Contract WTE]
	  ,[Paid WTE]
	  ,[Worked WTE]
	  ,[Headcount])
	SELECT  
	   [Employee Number]+'|'+[Cost Centre Code]+'|'+[Subjective Code]+'|'+[Analysis One Code]
	  ,[Fiscal Period]
      ,[Fiscal Year]
      ,[Fiscal Period Number]
      ,[Ledger Short Name]
      ,[Entity Code]
      ,[Cost Centre Code]
      ,[Subjective Code]
      ,[Analysis One Code]
      ,[Analysis Two Code]
      ,[Spare]
      ,[Payroll]
      ,[Pay Period]
      ,[Employee Number]
      ,[Employee Name]
      ,[Post Number]
      ,[Payscal]
      ,[Increment point]
      ,dateadd(DAY,-2,[Increment date])
      ,[Element Group]
      ,[Element Type description]
      ,[Pay Element Number]
      ,[Pay Element Description]
      ,[Currency]
      ,0 AS [Pay Amount]
      ,WTE 
      ,[Contract WTE]
      ,[Paid WTE] 
      ,[Worked WTE]
      ,Headcount
FROM [dbo].[PAY_WTETEMP]

DROP TABLE [dbo].[PAY_WTETEMP]

SELECT @myERROR = @@ERROR
IF @myERROR != 0 
BEGIN
SELECT 
    @RetCode = 0,
    @RetMsg = 'Error Encountered'
GOTO HANDLE_ERROR
END


COMMIT TRAN -- No Errors, so go ahead
SELECT @RetCode = 0,
       @RetMsg = 'updated successfully.'
RETURN


HANDLE_ERROR:  
ROLLBACK TRAN
RETURN


END

Thursday, September 15, 2011 12:02 PM

Answers

1

Sign in to vote
Your code contains a logic problem. You have an IF statement that precedes your BEGIN TRAN command.

One thing you can do is to move the BEGIN TRAN command so that it precedes your first select statement. Another alternative is to place your ROLLBACK TRAN command inside an IF statement and only execute the rollback if it is needed -- such as

IF @@TRANCOUNT > 0 ROLLBACK TRAN

Finally, you might want to read up on TRY and CATCH for your error handling.
- Edited by Kent Waldrop Thursday, September 15, 2011 12:11 PM
- Proposed as answer by Uwe RickenMVP Thursday, September 15, 2011 12:34 PM
- Marked as answer by Kalman Toth Tuesday, September 20, 2011 9:52 PM
Thursday, September 15, 2011 12:07 PM
1

Sign in to vote
Hallo,

that's quite simple - the error occures before the tran begin.

IF @CheckPeriod+1 <> @PayPeriod BEGIN SELECT @RetCode = 0, @RetMsg = 'Wrong Period' GOTO HANDLE_ERROR END

Two choices:

IF @CheckPeriod+1 <> @PayPeriod BEGIN SELECT @RetCode = 0, @RetMsg = 'Wrong Period' <strong>RETURN</strong> END

or the handling in the step out:

HANDLE_ERROR: WHILE @@TRANCOUNT != 0 ROLLBACK TRAN RETURN

Uwe Ricken

MCITP Database Administrator 2005
MCITP Database Administrator 2008
MCITP Microsoft SQL Server 2008, Database Development

db Berater GmbH
http://www-db-berater.de
- Proposed as answer by Uwe RickenMVP Thursday, September 15, 2011 12:34 PM
- Marked as answer by Kalman Toth Tuesday, September 20, 2011 9:52 PM
Thursday, September 15, 2011 12:07 PM
1

Sign in to vote
Use Try catch to trap error, if occured in this stored procedure.
velmurugan.s
- Proposed as answer by Naomi N Thursday, September 15, 2011 9:53 PM
- Marked as answer by Kalman Toth Tuesday, September 20, 2011 9:52 PM
Thursday, September 15, 2011 12:13 PM

I would want to add the following, since I was told by one of the moderators the other day to promote best practices as much as possible:

1. You are programming in T-SQL, not COBOL or Basic so control flow statements like GOTO, while still supported, should be avoided. GOTO had its place in the 50's when assembler programming constructs were pretty limited.

2. Your code has too many implicit assumptions: you are assuming table creations will be successful, you are assuming SQL Server will perform 100% of the time and drop those tables, etc. The following code illustrates how to achieve the same without the GOTOs or the implicit assumptions:

IF OBJECT_ID ( 'usp_GetErrorInfo', 'P' ) IS NOT NULL 
    DROP PROCEDURE usp_GetErrorInfo;
GO

-- Create procedure to retrieve error information.
CREATE PROCEDURE usp_GetErrorInfo
AS
SELECT
    ERROR_NUMBER() AS ErrorNumber
    ,ERROR_SEVERITY() AS ErrorSeverity
    ,ERROR_STATE() AS ErrorState
    ,ERROR_PROCEDURE() AS ErrorProcedure
    ,ERROR_LINE() AS ErrorLine
    ,ERROR_MESSAGE() AS ErrorMessage;
GO

CREATE PROCEDURE [dbo].[usp_dataimport]
	-- Add the parameters for the stored procedure here
	@PayPeriod int,
	@RetCode int = NULL OUTPUT,
	@RetMsg varchar(100) = NULL OUTPUT
AS

DECLARE @checkPeriod int 

BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

-- what if someone accidentally creates a second record in your current pay period table and you end up with 2 records instead?
-- you can avoid this by always retrieving only one record in your table.	
SELECT TOP 1 @CheckPeriod = dbo.[PAY_CURRENTPERIOD].MaxOfPeriod FROM dbo.[PAY_CURRENTPERIOD]

IF @CheckPeriod+1 <> @PayPeriod
BEGIN
    SELECT @RetCode = 0, @RetMsg = 'Wrong Period'
	-- SQL Server is not COBOL or BASIC, and while GOTO is valid, we do not 
	-- implement this type of control flow in our code
	-- GOTO HANDLE_ERROR
END
IF @RetCode = 0 RETURN;

-- Do not assume that the temp table creation will be successful, why not check
-- if the table exists before hand and cleanup if that's what you want?

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PAY_GBPTEMP]') AND type in (N'U'))
	TRUNCATE TABLE [dbo].[PAY_GBPTEMP];
ELSE
BEGIN
	-- note we are still not assuming the table creation will be successful
	BEGIN TRY
		CREATE TABLE [dbo].[PAY_GBPTEMP](
				[Fiscal Period] [varchar](50),
				[Fiscal Year] [smallint],
				[Fiscal Period Number] [smallint],
				[Ledger Short Name] [varchar](50),
				[Entity Code] [varchar](50),
				[Cost Centre Code] [varchar](50),
				[Subjective Code] [varchar](50),
				[Analysis One Code] [varchar](50),
				[Analysis Two Code] [varchar](50),
				[Spare] [varchar](50),
				[Payroll] [varchar](50),
				[Pay Period] [varchar](50),
				[Employee Number] [varchar](50),
				[Employee Name] [varchar](50),
				[Post Number] [varchar](50),
				[Payscal] [varchar](50),
				[Increment point] [varchar](50),
				[Increment date] [int],
				[Element Group] [varchar](50),
				[Element Type description] [varchar](50),
				[Pay Element Number] [varchar](50),
				[Pay Element Description] [varchar](50),
				[Currency] [varchar](50),
				[Pay Amount] [float],
				[Previous Month Payment] [float]);		
	END TRY
	BEGIN CATCH
			-- Execute error retrieval routine.
		EXECUTE usp_GetErrorInfo;
		SELECT @RetCode = 0, @RetMsg = 'Table creation failed for table PAY_GBPTEMP'
	END CATCH;		
END  

IF @RetCode = 0 RETURN;

-- Now let's attempt to upload your file
BEGIN TRANSACTION;
	BEGIN TRY
		-- Generate a constraint violation error.
		BULK INSERT [dbo].[PAY_GBPTEMP] FROM      
		'\\nhfps08\bnhfinance$\_GenLed Source\NHS_GL_Payroll_Analysis_Extrac_120911 PAY GBP.txt'  
		WITH (FIRSTROW = 2, FIELDTERMINATOR = '|', ROWTERMINATOR = '\n'); 
	END TRY
	BEGIN CATCH
		EXECUTE usp_GetErrorInfo;

		IF @@TRANCOUNT > 0
			ROLLBACK TRANSACTION;
	        
		-- make the errors meaningful
		SELECT @RetCode = 0, @RetMsg = 'Error encountered while loading payroll analysis file'
	END CATCH;
IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;

IF @RetCode = 0 RETURN;
END 
GO

MG.-
Mariano Gomez, MIS, MCITP, PMP
IntellPartners, LLC.
http://www.intellpartners.com/
Blog http://dynamicsgpblogster.blogspot.com/

Proposed as answer by Naomi N Thursday, September 15, 2011 9:53 PM
Marked as answer by Kalman Toth Tuesday, September 20, 2011 9:53 PM

Thursday, September 15, 2011 3:11 PM

All replies

1

Sign in to vote
Your code contains a logic problem. You have an IF statement that precedes your BEGIN TRAN command.

One thing you can do is to move the BEGIN TRAN command so that it precedes your first select statement. Another alternative is to place your ROLLBACK TRAN command inside an IF statement and only execute the rollback if it is needed -- such as

IF @@TRANCOUNT > 0 ROLLBACK TRAN

Finally, you might want to read up on TRY and CATCH for your error handling.
- Edited by Kent Waldrop Thursday, September 15, 2011 12:11 PM
- Proposed as answer by Uwe RickenMVP Thursday, September 15, 2011 12:34 PM
- Marked as answer by Kalman Toth Tuesday, September 20, 2011 9:52 PM
Thursday, September 15, 2011 12:07 PM
1

Sign in to vote
Hallo,

that's quite simple - the error occures before the tran begin.

IF @CheckPeriod+1 <> @PayPeriod BEGIN SELECT @RetCode = 0, @RetMsg = 'Wrong Period' GOTO HANDLE_ERROR END

Two choices:

IF @CheckPeriod+1 <> @PayPeriod BEGIN SELECT @RetCode = 0, @RetMsg = 'Wrong Period' <strong>RETURN</strong> END

or the handling in the step out:

HANDLE_ERROR: WHILE @@TRANCOUNT != 0 ROLLBACK TRAN RETURN

Uwe Ricken

MCITP Database Administrator 2005
MCITP Database Administrator 2008
MCITP Microsoft SQL Server 2008, Database Development

db Berater GmbH
http://www-db-berater.de
- Proposed as answer by Uwe RickenMVP Thursday, September 15, 2011 12:34 PM
- Marked as answer by Kalman Toth Tuesday, September 20, 2011 9:52 PM
Thursday, September 15, 2011 12:07 PM

Try This

USE [Staff_Data]
GO
/****** Object:  StoredProcedure [dbo].[usp_dataimport]    Script Date: 09/15/2011 12:32:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO







-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[usp_dataimport]
	-- Add the parameters for the stored procedure here
	@PayPeriod int,
	@RetCode int = NULL OUTPUT,
	@RetMsg varchar(100) = NULL OUTPUT

AS

DECLARE @myERROR int -- Local @@ERROR
DECLARE @checkPeriod int 

BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
SELECT @CheckPeriod = dbo.[PAY_CURRENTPERIOD].MaxOfPeriod FROM dbo.[PAY_CURRENTPERIOD]

IF @CheckPeriod+1 <> @PayPeriod
BEGIN
    SELECT 
    @RetCode = 0,
    @RetMsg = 'Wrong Period'
GOTO HANDLE_ERROR
END

BEGIN TRAN 

    -- Insert statements for procedure here
	CREATE TABLE	[dbo].[PAY_GBPTEMP](
					[Fiscal Period] [varchar](50),
					[Fiscal Year] [smallint],
					[Fiscal Period Number] [smallint],
					[Ledger Short Name] [varchar](50),
					[Entity Code] [varchar](50),
					[Cost Centre Code] [varchar](50),
					[Subjective Code] [varchar](50),
					[Analysis One Code] [varchar](50),
					[Analysis Two Code] [varchar](50),
					[Spare] [varchar](50),
					[Payroll] [varchar](50),
					[Pay Period] [varchar](50),
					[Employee Number] [varchar](50),
					[Employee Name] [varchar](50),
					[Post Number] [varchar](50),
					[Payscal] [varchar](50),
					[Increment point] [varchar](50),
					[Increment date] [int],
					[Element Group] [varchar](50),
					[Element Type description] [varchar](50),
					[Pay Element Number] [varchar](50),
					[Pay Element Description] [varchar](50),
					[Currency] [varchar](50),
					[Pay Amount] [float],
					[Previous Month Payment] [float])
					
BULK INSERT [dbo].[PAY_GBPTEMP] FROM      
'\\nhfps08\bnhfinance$\_GenLed Source\NHS_GL_Payroll_Analysis_Extrac_120911 PAY GBP.txt'  
WITH (FIRSTROW = 2, FIELDTERMINATOR = '|', ROWTERMINATOR = '\n') 

SELECT @myERROR = @@ERROR
IF @myERROR != 0 
BEGIN
SELECT 
    @RetCode = 0,
    @RetMsg = 'Error Encountered'
GOTO HANDLE_ERROR
END

   -- Insert statements for procedure here
	CREATE TABLE	[dbo].[PAY_WTETEMP](
					[Fiscal Period] [varchar](50),
					[Fiscal Year] [smallint],
					[Fiscal Period Number] [smallint],
					[Ledger Short Name] [varchar](50),
					[Entity Code] [varchar](50),
					[Cost Centre Code] [varchar](50),
					[Subjective Code] [varchar](50),
					[Analysis One Code] [varchar](50),
					[Analysis Two Code] [varchar](50),
					[Spare] [varchar](50),
					[Payroll] [varchar](50),
					[Pay Period] [varchar](50),
					[Employee Number] [varchar](50),
					[Employee Name] [varchar](50),
					[Post Number] [varchar](50),
					[Payscal] [varchar](50),
					[Increment point] [varchar](50),
					[Increment date] [int],
					[Element Group] [varchar](50),
					[Element Type description] [varchar](50),
					[Pay Element Number] [varchar](50),
					[Pay Element Description] [varchar](50),
					[Currency] [varchar](50),
					[WTE] [float],
					[Contract WTE] [float],
					[Paid WTE] [float],
					[Worked WTE] [float],
					[Headcount] [float])
					
BULK INSERT [dbo].[PAY_WTETEMP] FROM      
'\\nhfps08\bnhfinance$\_GenLed Source\NHS_GL_Payroll_WTE_Analysis_Ex_120911 WTE STAT.txt'  
WITH (FIRSTROW = 2, FIELDTERMINATOR = '|', ROWTERMINATOR = '\n') 

SELECT @myERROR = @@ERROR
IF @myERROR != 0 
BEGIN
SELECT 
    @RetCode = 0,
    @RetMsg = 'Error Encountered'
GOTO HANDLE_ERROR
END

DELETE FROM [Staff_Data].[dbo].[PAY_DETAIL]

	INSERT INTO [dbo].[PAY_DETAIL](
	   [KEY]	
	  ,[Fiscal Period]
      ,[Fiscal Year]
      ,[Fiscal Period Number]
      ,[Ledger Short Name]
      ,[Entity Code]
      ,[Cost Centre Code]
      ,[Subjective Code]
      ,[Analysis One Code]
      ,[Analysis Two Code]
      ,[Spare]
      ,[Payroll]
      ,[Pay Period]
      ,[Employee Number]
      ,[Employee Name]
      ,[Post Number]
      ,[Payscal]
      ,[Increment point]
      ,[Increment date]
      ,[Element Group]
      ,[Element Type description]
      ,[Pay Element Number]
      ,[Pay Element Description]
      ,[Currency]
      ,[Pay Amount]
	  ,[WTE]
	  ,[Contract WTE]
	  ,[Paid WTE]
	  ,[Worked WTE]
	  ,[Headcount])
	SELECT  
	   [Employee Number]+'|'+[Cost Centre Code]+'|'+[Subjective Code]+'|'+[Analysis One Code]
	  ,[Fiscal Period]
      ,[Fiscal Year]
      ,[Fiscal Period Number]
      ,[Ledger Short Name]
      ,[Entity Code]
      ,[Cost Centre Code]
      ,[Subjective Code]
      ,[Analysis One Code]
      ,[Analysis Two Code]
      ,[Spare]
      ,[Payroll]
      ,[Pay Period]
      ,[Employee Number]
      ,[Employee Name]
      ,[Post Number]
      ,[Payscal]
      ,[Increment point]
      ,dateadd(DAY,-2,[Increment date])
      ,[Element Group]
      ,[Element Type description]
      ,[Pay Element Number]
      ,[Pay Element Description]
      ,[Currency]
      ,[Pay Amount]
      ,0 AS WTE 
      ,0 AS [Contract WTE]
      ,0 AS [Paid WTE] 
      ,0 AS [Worked WTE]
      ,0 AS Headcount
FROM [dbo].[PAY_GBPTEMP]

DROP TABLE [dbo].[PAY_GBPTEMP]

SELECT @myERROR = @@ERROR
IF @myERROR != 0 
BEGIN
SELECT 
    @RetCode = 0,
    @RetMsg = 'Error Encountered'
GOTO HANDLE_ERROR
END

	INSERT INTO [dbo].[PAY_DETAIL](
	   [KEY]	
	  ,[Fiscal Period]
      ,[Fiscal Year]
      ,[Fiscal Period Number]
      ,[Ledger Short Name]
      ,[Entity Code]
      ,[Cost Centre Code]
      ,[Subjective Code]
      ,[Analysis One Code]
      ,[Analysis Two Code]
      ,[Spare]
      ,[Payroll]
      ,[Pay Period]
      ,[Employee Number]
      ,[Employee Name]
      ,[Post Number]
      ,[Payscal]
      ,[Increment point]
      ,[Increment date]
      ,[Element Group]
      ,[Element Type description]
      ,[Pay Element Number]
      ,[Pay Element Description]
      ,[Currency]
      ,[Pay Amount]
	  ,[WTE]
	  ,[Contract WTE]
	  ,[Paid WTE]
	  ,[Worked WTE]
	  ,[Headcount])
	SELECT  
	   [Employee Number]+'|'+[Cost Centre Code]+'|'+[Subjective Code]+'|'+[Analysis One Code]
	  ,[Fiscal Period]
      ,[Fiscal Year]
      ,[Fiscal Period Number]
      ,[Ledger Short Name]
      ,[Entity Code]
      ,[Cost Centre Code]
      ,[Subjective Code]
      ,[Analysis One Code]
      ,[Analysis Two Code]
      ,[Spare]
      ,[Payroll]
      ,[Pay Period]
      ,[Employee Number]
      ,[Employee Name]
      ,[Post Number]
      ,[Payscal]
      ,[Increment point]
      ,dateadd(DAY,-2,[Increment date])
      ,[Element Group]
      ,[Element Type description]
      ,[Pay Element Number]
      ,[Pay Element Description]
      ,[Currency]
      ,0 AS [Pay Amount]
      ,WTE 
      ,[Contract WTE]
      ,[Paid WTE] 
      ,[Worked WTE]
      ,Headcount
FROM [dbo].[PAY_WTETEMP]

DROP TABLE [dbo].[PAY_WTETEMP]

SELECT @myERROR = @@ERROR
IF @myERROR != 0 
BEGIN
SELECT 
    @RetCode = 0,
    @RetMsg = 'Error Encountered'
GOTO HANDLE_ERROR
END


COMMIT TRAN -- No Errors, so go ahead
SELECT @RetCode = 0,
       @RetMsg = 'updated successfully.'
RETURN


HANDLE_ERROR:  
If @@TRANCOUNT>0
	Begin
		ROLLBACK TRAN
	End
RETURN


END

Mark as Answer If Reply Was Helpful Thanks Kuldeep Bisht Technical Lead @ Simplion Technologies http://mssqlguide.kuldeepbisht.com

Thursday, September 15, 2011 12:09 PM

1

Sign in to vote
Have a look at this link on Try Catch and implement this in your stored proc.

http://msdn.microsoft.com/en-us/library/ms175976.aspx

You put your main code into the Try part of the transaction and if there is an issue then it goes to the catch part and executes your rollback.

I hope this helps.
Warwick Rudd MCT MCITP SQL Server 2008 Admin
- Proposed as answer by Naomi N Thursday, September 15, 2011 9:53 PM
Thursday, September 15, 2011 12:09 PM
1

Sign in to vote
Use Try catch to trap error, if occured in this stored procedure.
velmurugan.s
- Proposed as answer by Naomi N Thursday, September 15, 2011 9:53 PM
- Marked as answer by Kalman Toth Tuesday, September 20, 2011 9:52 PM
Thursday, September 15, 2011 12:13 PM
0

Sign in to vote

Thank you all so much.

Simple when you know how, easy answer was as a couple of you pointed out was that I was encountering an error and sending to the error_handler before I even started my transaction.

thanks also for the other tips!

Dominic

Thursday, September 15, 2011 12:22 PM