Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
2241 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
2241 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  
 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.07 seconds. Powered By: Snitz Forums 2000