Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
mohan123
Constraint Violating Yak Guru
252 Posts |
Posted - 2013-04-08 : 06:31:51
|
hello all,in my procedure insert is happening while trying to update record [Code]Msg 266, Level 16, State 2, Procedure usp_HandleException, Line 0Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.error is coming like this...i am unable to find the error where lies in this Proci am giving the outlook of store procedure where problem lies[CODE]BEGIN TRY SET NOCOUNT ON SET XACT_ABORT ON -- Check if valid Application User ID is passed IF ( @i_AppUserId IS NULL ) OR ( @i_AppUserId <= 0 ) BEGIN RAISERROR ( N'Invalid Application User ID %d passed.' ,17 ,1 ,@i_AppUserId ) END DECLARE @l_TranStarted BIT = 0 IF ( @@TRANCOUNT = 0 ) BEGIN BEGIN TRANSACTION SET @l_TranStarted = 1 -- Indicator for start of transactions END ELSE BEGIN SET @l_TranStarted = 0 END[/code]BEGIN DECLARE @i_ID INT IF EXISTS ( SELECT 1 FROM Metric m INNER JOIN PopulationDefinition nr ON m.NumeratorID = nr.PopulationDefinitionID WHERE Name = @v_Name AND nr.NumeratorType = @v_NumeratorType AND nr.PopulationDefinitionID = @i_NumeratorID ) BEGIN RAISERROR ( N'Already Metric exists.' ,17 ,1 ) WITH NOWAIT END[/code]Now insert starts SET @o_userId = SCOPE_IDENTITY() ;[CODE] BEGIN IF EXISTS ( SELECT 1 FROM Metric m INNER JOIN PopulationDefinition nr ON m.NumeratorID = nr.PopulationDefinitionID WHERE Name = @v_Name AND nr.NumeratorType = @v_NumeratorType AND nr.PopulationDefinitionID = @i_NumeratorID ) BEGIN RAISERROR ( N'Already Metric exists.' ,17 ,1 ) WITH NOWAIT END ELSE BEGIN IF ( @l_TranStarted = 1 ) -- If transactions are there, then commit BEGIN SET @l_TranStarted = 0 COMMIT TRANSACTION ROLLBACK TRANSACTION END END TRY BEGIN CATCH -- Handle exception DECLARE @i_ReturnedErrorID INT EXECUTE @i_ReturnedErrorID = dbo.usp_HandleException @i_UserId = @i_AppUserId RETURN @i_ReturnedErrorIDEND CATCH [/code]Error number : 50000Suggest meP.V.P.MOhan |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-04-08 : 06:58:39
|
I think that is BEGIN...END Problem --Last ELSE block in your codeELSEBEGIN IF ( @l_TranStarted = 1 ) -- If transactions are there, then commit BEGIN SET @l_TranStarted = 0 COMMIT TRANSACTION END ELSE ROLLBACK TRANSACTIONEND |
|
|
mohan123
Constraint Violating Yak Guru
252 Posts |
Posted - 2013-04-08 : 07:58:37
|
Thanks ChanduP.V.P.MOhan |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-04-08 : 08:43:45
|
quote: Originally posted by mohan123 Thanks ChanduP.V.P.MOhan
Welcome--Chandu |
|
|
|
|
|
|
|