SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 In my store Proc i can insert but unable to update
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 04/08/2013 :  06:31:51  Show Profile  Reply with Quote
hello all,

in my procedure insert is happening while trying to update record 
[Code]
Msg 266, Level 16, State 2, Procedure usp_HandleException, Line 0
Transaction 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 Proc

i 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

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

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_ReturnedErrorID
END CATCH

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 04/08/2013 :  06:58:39  Show Profile  Reply with Quote
I think that is BEGIN...END Problem
 --Last ELSE block in your code
ELSE
BEGIN
	IF ( @l_TranStarted = 1 ) -- If transactions are there, then commit
	BEGIN
		SET @l_TranStarted = 0
		COMMIT TRANSACTION
	END
	ELSE
		ROLLBACK TRANSACTION
END
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 04/08/2013 :  07:58:37  Show Profile  Reply with Quote
Thanks Chandu

P.V.P.MOhan
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 04/08/2013 :  08:43:45  Show Profile  Reply with Quote
quote:
Originally posted by mohan123

Thanks Chandu
P.V.P.MOhan


Welcome

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000