i am getting this error
can any one tell me what's the mistake here i am new to sp's
thanks in advance
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1
Code:
BEGIN TRY
BEGIN TRAN
IF( @iHId =0)
IF not exists(select HDR.AOHId FROM dbo.tblXHdrApplicationOptions HDR INNER JOIN dbo.tblXDtlApplicationSuper DTL ON HDR.AOHId = DTL.ASDHId
where DTL.ASDChildName=@strChildName )
BEGIN
INSERT INTO dbo.tblXHdrApplicationOptions (AOHRootIndex, AOHRootName,AOHUser, AOHDateTime)
VALUES (@iRootIndx, @strRootName,@strUserName, GETDATE())
DECLARE @iAOHId INT
SET @iAOHId = SCOPE_IDENTITY();
INSERT INTO dbo.tblXDtlApplicationSuper ( ASDHId , ASDChildName,ASDIsEOMYN,ASDIsEachPayYN,ASDYearsIfEomIsYes,ASDUser,ASDDateTime)
VALUES(@iAOHId, @strChildName,@bIsEomYN,@bEachPayYN,@strYearifEOMisYes, @strUserName, GETDATE())
DECLARE @ASDHId INT
SET @ASDHId = SCOPE_IDENTITY();
INSERT INTO tblXDtlApplicationSuperdetails (ASDDHID, ASDDChildName,ASDDMonthCode, ASDDEOMDate,ASDDUser,ASDDDateTime)
VALUES (@iAOHId, @strChildName,@strMonthCode, @dtpEOMDate,@strUserName,GETDATE())
DECLARE @ASDDHID INT
SET @iAOHId = SCOPE_IDENTITY()
COMMIT TRAN
RETURN 0
END
ELSE
IF exists(select HDR.AOHId FROM dbo.tblXHdrApplicationOptions HDR INNER JOIN dbo.tblXDtlApplicationSuper DTL ON HDR.AOHId = DTL.ASDHId
where DTL.ASDChildName=@strChildName )
--
BEGIN
UPDATE dbo.tblXHdrApplicationOptions SET
AOHDateTime = GETDATE();
UPDATE dbo.tblXDtlApplicationSuper SET ASDIsEOMYN=@bIsEomYN ,ASDIsEachPayYN=@bEachPayYN ,
ASDYearsIfEomIsYes=@strYearifEOMisYes ,ASDUser = @strUserName, ASDDateTime = GETDATE()
WHERE ASDHId = @iHId ;
UPDATE tblXDtlApplicationSuperdetails SET ASDDChildName=@strChildName,ASDDMonthCode=@strMonthCode,
ASDDEOMDate=@dtpEOMDate,ASDDUser=@strUserName,ASDDDateTime=GETDATE() WHERE ASDDHID=@iAOHId
COMMIT TRAN
RETURN 0
END
END TRY
BEGIN CATCH
ROLLBACK TRAN;
DECLARE @ErrorMessage NVARCHAR(4000),
@ErrorSeverity INT,@ErrorState INT;
SET @ErrorMessage ='-1'+ERROR_MESSAGE();
SET @ErrorSeverity = ERROR_SEVERITY();
SET @ErrorState = ERROR_STATE();
RAISERROR(@ErrorMessage,@ErrorSeverity,@ErrorState);
END CATCH