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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 In my store Proc i can insert but unable to update

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

[/code]


Error number : 50000

Suggest me

P.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 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

252 Posts

Posted - 2013-04-08 : 07:58:37
Thanks Chandu

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-08 : 08:43:45
quote:
Originally posted by mohan123

Thanks Chandu
P.V.P.MOhan


Welcome

--
Chandu
Go to Top of Page
   

- Advertisement -