COMMIT / ROLLBACK usage WITH EXEC giving error on MS SQL Server

MS SQL Server query giving a script error when I use an EXEC command (Stored Procedure) just after or before COMMIT / ROLLBACK. It is ok when I take the EXEC command lines out.

Could you help please, is there any work around for this?

Thanks,
USE [AdventureWorks]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_test]
AS
BEGIN
	SET NOCOUNT ON;
	--////////////////////////////////////////////
	Declare @ErrorCode int
Select @ErrorCode = @@Error

	Declare @TransactionCountOnEntry int
If @ErrorCode = 0
Begin
   Select @TransactionCountOnEntry = @@TranCount
   BEGIN TRANSACTION
End

Declare @Temp nchar(10)
SELECT @Temp = 'Insertion'
INSERT INTO dbo.Test(logo) VALUES(@Temp)

--////////////////////////////////////////////////
		IF @@TRANCOUNT > @TransactionCountOnEntry
		BEGIN
		   IF @ErrorCode = 0		  
		  COMMIT TRANSACTION
		  EXEC xp_sendmail @recipients=N'someone@somewhere.com', @Subject='JOb has run successfully'

		   ELSE
		  ROLLBACK TRANSACTION
		  EXEC xp_sendmail @recipients=N'someone@somewhere.com', @Subject='JOb has failed'
		END
--/////////////////////////////////////////
END
GO

Open in new window

Serdar DemirkolApplication Developer & DBAAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RiteshShahCommented:
I affraid, I don't understand your question 100% but even try this code.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rajkumar GsSoftware EngineerCommented:
#1:- You have opened the Transaction inside the condition
If @ErrorCode = 0
      BEGIN TRANSACTION

But you are trying ROLLBACK TRANSACTION for else of condition that started Transaction

eg:- If @ErrorCode = 1 BEGIN TRANSACTION code will not get executed. But ROLLBACK TRANSACTION can occur.

This is one that I felt wrong in the first sight.


#2:- It needs BEGIN.. END to group the set of commands that need to be executed inside IF.


Attached the corrected script

Raj
USE [AdventureWorks] 
GO 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
 
ALTER PROCEDURE [dbo].[sp_test] 
AS 
BEGIN 
        SET NOCOUNT ON; 
        --//////////////////////////////////////////// 
        Declare @ErrorCode int 
Select @ErrorCode = @@Error 

   BEGIN TRANSACTION 
 
        Declare @TransactionCountOnEntry int 
If @ErrorCode = 0 
Begin 
   Select @TransactionCountOnEntry = @@TranCount 

End 
 
Declare @Temp nchar(10) 
SELECT @Temp = 'Insertion' 
INSERT INTO dbo.Test(logo) VALUES(@Temp) 
 
--//////////////////////////////////////////////// 
IF @@TRANCOUNT > @TransactionCountOnEntry 
BEGIN 
	IF @ErrorCode = 0  
		BEGIN             
			COMMIT TRANSACTION 
			EXEC xp_sendmail @recipients=N'someone@somewhere.com', @Subject='JOb has run successfully' 
		END
	ELSE 
		BEGIN             
			ROLLBACK TRANSACTION 
			EXEC xp_sendmail @recipients=N'someone@somewhere.com', @Subject='JOb has failed' 
		END
END 
--///////////////////////////////////////// 
END 
GO

Open in new window

0
Lee SavidgeCommented:
Try this. You're missing the begin and end in the if statement.

Lee
IF @@TRANCOUNT > @TransactionCountOnEntry
                BEGIN
                   IF @ErrorCode = 0              
                   BEGIN
                    COMMIT TRANSACTION
                    EXEC xp_sendmail @recipients=N'someone@somewhere.com', @Subject='JOb has run successfully'
                   END
                   ELSE
                   BEGIN
                    ROLLBACK TRANSACTION
                    EXEC xp_sendmail @recipients=N'someone@somewhere.com', @Subject='JOb has failed'
                   END
                END

Open in new window

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Download your Guide
Lee SavidgeCommented:
If your IF statement has multiple statements inside it then you need to surround the block with BEGIN...END for the IF part and the ELSE part.

Lee
0
Serdar DemirkolApplication Developer & DBAAuthor Commented:
It works ok now.
Thanks guys,
0
RiteshShahCommented:
surprised!!! where my code went? It was there in very first answer of this question but not there now.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.