SQL Server transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statementsHow do you clear the SQL Server transaction log?Count(*) vs Count(1) - SQL ServerSQL Server query - Selecting COUNT(*) with DISTINCTWhat is the use of GO in SQL Server Management Studio & Transact SQL?Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT?Reset identity seed after deleting records in SQL ServerTransaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statementsSQL Server error - Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements
Journal losing indexing services
How to align and center standalone amsmath equations?
Wrapping Cryptocurrencies for interoperability sake
Why does Async/Await work properly when the loop is inside the async function and not the other way around?
MAXDOP Settings for SQL Server 2014
How will losing mobility of one hand affect my career as a programmer?
How do I extrude a face to a single vertex
What's the difference between 違法 and 不法?
How must one send away the mother bird?
Is there a conventional notation or name for the slip angle?
How should I respond when I lied about my education and the company finds out through background check?
Is there a word to describe the feeling of being transfixed out of horror?
Could the E-bike drivetrain wear down till needing replacement after 400 km?
Diode in opposite direction?
Longest common substring in linear time
What major Native American tribes were around Santa Fe during the late 1850s?
Will adding a BY-SA image to a blog post make the entire post BY-SA?
List of people who lose a child in תנ"ך
Can a significant change in incentives void an employment contract?
Bob has never been a M before
Is it possible to have a strip of cold climate in the middle of a planet?
Do Legal Documents Require Signing In Standard Pen Colors?
How do you respond to a colleague from another team when they're wrongly expecting that you'll help them?
Can the Supreme Court overturn an impeachment?
SQL Server transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements
How do you clear the SQL Server transaction log?Count(*) vs Count(1) - SQL ServerSQL Server query - Selecting COUNT(*) with DISTINCTWhat is the use of GO in SQL Server Management Studio & Transact SQL?Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT?Reset identity seed after deleting records in SQL ServerTransaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statementsSQL Server error - Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements
0
In the below stored procedure, I'm getting the error mentioned in some cases, what am I doing wrong here?
Error:
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1
Stored procedure:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[prcInsDataFeesForVersion]
(@ContractId INT,
@UserId INT,
@VersionNo INT,
@SelectAll BIT,
@SubProductList NVARCHAR(MAX),
@ConvRateFlag BIT)
AS
BEGIN
SET NOCOUNT ON
DECLARE @RetVal INT,
@ErrMsg VARCHAR(255),
@FunctionId INT = 1234,
@AppId VARCHAR(20) = 'ID1021',
@FeesOverrideFlg BIT = 1,
@ContractProdFeeVerSummId INT
CREATE TABLE #tblMasterData
(
ProdCd INT,
ProdName NVARCHAR(255),
SubProdCd INT,
SubProdName NVARCHAR(255),
ProdTypeCd INT,
ProdDeliveryTypCd INT,
ProdStatusCd INT,
ProdStatusDt DATETIME,
CreatedDate DATETIME
)
CREATE TABLE #tblContractInterCulturalDataFiltered
(
ContractID INT
, ContractProdID INT
, ProdCd INT
, ProdName NVARCHAR(255)
, ContractSubProdID INT
, SubProdCd INT
, SubProdName NVARCHAR(255)
, ContractProdStatusCd INT
, ContractProdStatusDt DATETIME
, ContractSubProdStatusCd INT
, ContractSubProdStatusDt DATETIME
, ContractProdFeeId INT
, FeeTypCd INT
, RowMatrixParameterCd INT
, CreatedDate DATETIME
)
CREATE TABLE #tblContractInterCulturalDataFilteredInsert
(
ContractID INT
, ContractProdID INT
, ProdCd INT
, ProdName NVARCHAR(255)
, ContractSubProdID INT
, SubProdCd INT
, SubProdName NVARCHAR(255)
, ContractProdStatusCd INT
, ContractProdStatusDt DATETIME
, ContractSubProdStatusCd INT
, ContractSubProdStatusDt DATETIME
, FeeTypCd INT
, FeeDeterminantCd INT
, RowMatrixParameterCd INT
, ProdFeeCurrCd INT
, RowParameterValueCd INT
, ColumnParameterValueCd INT
, ProdFeePct REAL
, RangeFromDayCnt INT
, RangeToDayCnt INT
, ProdFeeLevelMinAmt DECIMAL
, ProdFeeLevelMaxAmt DECIMAL
, ProdFeeAmt DECIMAL
, ContractProdFeeId INT
, CreatedDate DATETIME
)
CREATE TABLE #tblContractInterCulturalDataFilteredUpdate
(
ContractID INT
, ContractProdID INT
, ProdCd INT
, ProdName NVARCHAR(255)
, ContractSubProdID INT
, SubProdCd INT
, SubProdName NVARCHAR(255)
, ContractProdStatusCd INT
, ContractProdStatusDt DATETIME
, ContractSubProdStatusCd INT
, ContractSubProdStatusDt DATETIME
, FeeTypCd INT
, FeeDeterminantCd INT
, RowMatrixParameterCd INT
, ProdFeeCurrCd INT
, RowParameterValueCd INT
, ColumnParameterValueCd INT
, ProdFeePct REAL
, RangeFromDayCnt INT
, RangeToDayCnt INT
, ProdFeeLevelMinAmt DECIMAL
, ProdFeeLevelMaxAmt DECIMAL
, ProdFeeAmt DECIMAL
, ContractProdFeeId INT
, CreatedDate DATETIME
)
CREATE TABLE #tblSelContractSubProds
(
ContractSubProdId INT
)
BEGIN TRY
BEGIN TRANSACTION
IF(@SelectAll=0 AND @SubProductList IS NOT NULL)
BEGIN
INSERT INTO #tblSelContractSubProds
SELECT intValue FROM dbo.udfSplit(@SubProductList,',',1)
END
INSERT INTO #tblMasterData(
ProdCd
, ProdName
, SubProdCd
, SubProdName
, ProdTypeCd
, ProdDeliveryTypCd
, ProdStatusCd
, ProdStatusDt
, CreatedDate
)
SELECT P.ProdCd
, PC.Descr
, S.SubProdCd
, SC.Descr
, P.ProdTypeCd
, P.ProdDeliveryTypCd
, P.ProdStatusCd
, P.ProdStatusDt
, GETDATE()
FROM tblProduct(NOLOCK) as P
INNER JOIN tblCode(NOLOCK) as PC ON P.ProdCd=PC.Cd and PC.Typ=1053
INNER JOIN tblSubProdDetail(NOLOCK) as S ON P.ProdCd=S.ProdCd
INNER JOIN tblCode(NOLOCK) as SC ON S.SubProdCd=SC.Cd and SC.Typ=1053
WHERE P.ProdDeliveryTypCd=3 and P.ProdStatusCd=1
IF(@SelectAll=1)
BEGIN
INSERT INTO #tblContractInterCulturalDataFiltered(
ContractID
, ContractProdID
, ProdCd
, ProdName
, ContractSubProdID
, SubProdCd
, SubProdName
, ContractProdStatusCd
, ContractProdStatusDt
, ContractSubProdStatusCd
, ContractSubProdStatusDt
, ContractProdFeeId
, FeeTypCd
, RowMatrixParameterCd
, CreatedDate
)
SELECT DISTINCT CP.ContractId,
CP.ContractProdId,
CP.ProdCd,
CPC.Descr,
CSP.ContractSubProdID,
CSP.SubProdCd,
CSPC.Descr,
CP.ContractProdStatusCd,
CP.ContractProdStatusDt,
CSP.ContractSubProdStatusCd,
CSP.ContractSubProdStatusDt,
CPF.ContractProdFeeId,
CPF.FeeTypCd,
CPF.RowMatrixParameterCd,
Getdate()
FROM #tblMasterData(NOLOCK) MD
INNER JOIN tblContractProd(NOLOCK) CP ON MD.ProdCd=CP.ProdCd
INNER JOIN tblContractSubProd(NOLOCK) CSP ON CP.ContractProdId=CSP.ContractProdId
INNER JOIN tblCode(NOLOCK) CPC ON CP.ProdCd=CPC.Cd and CPC.Typ=1053
INNER JOIN tblCode(NOLOCK) CSPC ON CSP.SubProdCd=CSPC.Cd and CSPC.Typ=1053
INNER JOIN tblContractProdFee(NOLOCK) CPF ON CP.ContractProdID=CPF.ContractProdId AND CSP.ContractSubProdID=CPF.ContractSubProdId
WHERE ContractId=@ContractId AND CP.ContractProdStatusCd=1 AND CSP.ContractSubProdStatusCd=1
AND CPF.ContractProdFeeTypStatusCd=1 AND CP.StandardProdIndCd=1 AND CSP.StdSubProdIndCd=1
END
ELSE
BEGIN
IF(@SubProductList IS NOT NULL)
BEGIN
INSERT INTO #tblContractInterCulturalDataFiltered(
ContractID
, ContractProdID
, ProdCd
, ProdName
, ContractSubProdID
, SubProdCd
, SubProdName
, ContractProdStatusCd
, ContractProdStatusDt
, ContractSubProdStatusCd
, ContractSubProdStatusDt
, ContractProdFeeId
, FeeTypCd
, RowMatrixParameterCd
, CreatedDate
)
SELECT DISTINCT CP.ContractId,
CP.ContractProdId,
CP.ProdCd,
CPC.Descr,
CSP.ContractSubProdID,
CSP.SubProdCd,
CSPC.Descr,
CP.ContractProdStatusCd,
CP.ContractProdStatusDt,
CSP.ContractSubProdStatusCd,
CSP.ContractSubProdStatusDt,
CPF.ContractProdFeeId,
CPF.FeeTypCd,
CPF.RowMatrixParameterCd,
Getdate()
FROM #tblMasterData(NOLOCK) MD
INNER JOIN tblContractProd(NOLOCK) CP ON MD.ProdCd=CP.ProdCd
INNER JOIN tblContractSubProd(NOLOCK) CSP ON CP.ContractProdId=CSP.ContractProdId
INNER JOIN tblCode(NOLOCK) CPC ON CP.ProdCd=CPC.Cd and CPC.Typ=1053
INNER JOIN tblCode(NOLOCK) CSPC ON CSP.SubProdCd=CSPC.Cd and CSPC.Typ=1053
INNER JOIN tblContractProdFee(NOLOCK) CPF ON CP.ContractProdID=CPF.ContractProdId AND CSP.ContractSubProdID=CPF.ContractSubProdId
INNER JOIN #tblSelContractSubProds(NOLOCK) SCSP ON CSP.ContractSubProdId=SCSP.ContractSubProdId
WHERE ContractId=@ContractId AND CP.ContractProdStatusCd=1 AND CSP.ContractSubProdStatusCd=1
AND CPF.ContractProdFeeTypStatusCd=1 AND CP.StandardProdIndCd=1 AND CSP.StdSubProdIndCd=1
END
END
INSERT INTO #tblContractInterCulturalDataFilteredUpdate(
ContractID
, ContractProdID
, ProdCd
, ProdName
, ContractSubProdID
, SubProdCd
, SubProdName
, ContractProdStatusCd
, ContractProdStatusDt
, ContractSubProdStatusCd
, ContractSubProdStatusDt
, FeeTypCd
, FeeDeterminantCd
, RowMatrixParameterCd
, ProdFeeCurrCd
, RowParameterValueCd
, ColumnParameterValueCd
, ProdFeePct
, RangeFromDayCnt
, RangeToDayCnt
, ProdFeeLevelMinAmt
, ProdFeeLevelMaxAmt
, ProdFeeAmt
, ContractProdFeeId
, CreatedDate
)
SELECT DISTINCT CICF.ContractId,
CICF.ContractProdId,
CICF.ProdCd,
CICF.ProdName,
CICF.ContractSubProdID,
CICF.SubProdCd,
CICF.SubProdName,
CICF.ContractProdStatusCd,
CICF.ContractProdStatusDt,
CICF.ContractSubProdStatusCd,
CICF.ContractSubProdStatusDt,
CPFM.FeeTypCd,
CPFM.FeeDeterminantCd,
CPFM.RowMatrixParameterCd,
CPFM.ProdFeeCurrCd,
CPFM.RowParameterValueCd,
CPFM.ColumnParameterValueCd,
CPFM.ProdFeePct,
CPFM.RangeFromDayCnt,
CPFM.RangeToDayCnt,
CPFM.ProdFeeLevelMinAmt,
CPFM.ProdFeeLevelMaxAmt,
CPFM.ProdFeeAmt,
CICF.ContractProdFeeId,
Getdate()
FROM #tblContractInterCulturalDataFiltered(NOLOCK) CICF
INNER JOIN tblContractProdFeeMatrix(NOLOCK) CPFM ON CICF.ProdCd=CPFM.FeeMatrixProdCd AND CICF.SubProdCd=CPFM.FeeMatrixSubProdCd AND CICF.FeeTypCd=CPFM.FeeTypCd
INNER JOIN tblContractProdFeeMatrixVersion(NOLOCK) CPFMV ON CPFM.ContractProdFeeMatrixVersionId=CPFMV.ContractProdFeeMatrixVersionId
INNER JOIN tblContractProdFee(NOLOCK) CP ON CICF.ContractProdID=CP.ContractProdId AND CICF.ContractSubProdID=CP.ContractSubProdId AND CICF.ContractProdFeeId=CP.ContractProdFeeId
AND CPFM.FeeDeterminantCd=CP.FeeDeterminantCd AND CPFM.RowMatrixParameterCd =CP.RowMatrixParameterCd
WHERE CICF.ContractId=@ContractId AND CPFMV.ProdFeeMatrixVerNo=@VersionNo AND CICF.ContractProdStatusCd=1 AND CICF.ContractSubProdStatusCd=1 AND CP.ContractProdFeeTypStatusCd=1
AND CPFM.RowMatrixParameterCd IN(6,4)
AND EXISTS(SELECT PFM.ContractProdFeeId from tblProdFeeMatrix(NOLOCK)PFM WHERE CICF.ContractProdFeeId=PFM.ContractProdFeeId)
IF EXISTS(Select 1 From #tblContractInterCulturalDataFilteredUpdate(NOLOCK))
BEGIN
DELETE PFM
FROM tblProdFeeMatrix(NOLOCK) PFM
INNER JOIN #tblContractInterCulturalDataFilteredUpdate(NOLOCK) TIFU ON TIFU.ContractProdFeeId=PFM.ContractProdFeeId
END
INSERT INTO #tblContractInterCulturalDataFilteredInsert(
ContractID
, ContractProdID
, ProdCd
, ProdName
, ContractSubProdID
, SubProdCd
, SubProdName
, ContractProdStatusCd
, ContractProdStatusDt
, ContractSubProdStatusCd
, ContractSubProdStatusDt
, FeeTypCd
, FeeDeterminantCd
, RowMatrixParameterCd
, ProdFeeCurrCd
, RowParameterValueCd
, ColumnParameterValueCd
, ProdFeePct
, RangeFromDayCnt
, RangeToDayCnt
, ProdFeeLevelMinAmt
, ProdFeeLevelMaxAmt
, ProdFeeAmt
, ContractProdFeeId
, CreatedDate
)
SELECT DISTINCT CICF.ContractId,
CICF.ContractProdId,
CICF.ProdCd,
CICF.ProdName,
CICF.ContractSubProdID,
CICF.SubProdCd,
CICF.SubProdName,
CICF.ContractProdStatusCd,
CICF.ContractProdStatusDt,
CICF.ContractSubProdStatusCd,
CICF.ContractSubProdStatusDt,
CPFM.FeeTypCd,
CPFM.FeeDeterminantCd,
CPFM.RowMatrixParameterCd,
CPFM.ProdFeeCurrCd,
CPFM.RowParameterValueCd,
CPFM.ColumnParameterValueCd,
CPFM.ProdFeePct,
CPFM.RangeFromDayCnt,
CPFM.RangeToDayCnt,
CPFM.ProdFeeLevelMinAmt,
CPFM.ProdFeeLevelMaxAmt,
CPFM.ProdFeeAmt,
CICF.ContractProdFeeId,
Getdate()
FROM #tblContractInterCulturalDataFiltered(NOLOCK) CICF
INNER JOIN tblContractProdFeeMatrix(NOLOCK) CPFM ON CICF.ProdCd=CPFM.FeeMatrixProdCd AND CICF.SubProdCd=CPFM.FeeMatrixSubProdCd AND CICF.FeeTypCd=CPFM.FeeTypCd
INNER JOIN tblContractProdFeeMatrixVersion(NOLOCK) CPFMV ON CPFM.ContractProdFeeMatrixVersionId=CPFMV.ContractProdFeeMatrixVersionId
INNER JOIN tblContractProdFee(NOLOCK) CP ON CICF.ContractProdID=CP.ContractProdId AND CICF.ContractSubProdID=CP.ContractSubProdId AND CICF.ContractProdFeeId=CP.ContractProdFeeId
AND CPFM.FeeDeterminantCd=CP.FeeDeterminantCd AND CPFM.RowMatrixParameterCd =CP.RowMatrixParameterCd
WHERE CICF.ContractId=@ContractId AND CPFMV.ProdFeeMatrixVerNo=@VersionNo AND CICF.ContractProdStatusCd=1 AND CICF.ContractSubProdStatusCd=1 AND CP.ContractProdFeeTypStatusCd=1
AND CPFM.RowMatrixParameterCd IN(6,4)
IF EXISTS(Select 1 From #tblContractInterCulturalDataFilteredInsert(NOLOCK))
BEGIN
INSERT INTO [dbo].[tblProdFeeMatrix]
([ContractProdFeeId]
,[ContractBundleFeeId]
,[ContractProdBundleAncilFeeId]
,[RowParameterValueCd]
,[ColumnParameterValueCd]
,[ProdFeeAmt]
,[ProdFeeCurrCd]
,[ProdFeePct]
,[RangeFromDayCnt]
,[RangeToDayCnt]
,[RangeFromPct]
,[RangeToPct]
,[RangeFromAmt]
,[RangeToAmt]
,[ProdFeeLevelMinAmt]
,[ProdFeeLevelMaxAmt]
,[FeeNotApplyIndCd]
,[CreateId]
,[CreateDt]
,[UpdateId]
,[UpdateDt]
,[FunctionId])
SELECT DISTINCT
TIFI.ContractProdFeeID,
0,
0,
TIFI.RowParameterValueCd,
TIFI.ColumnParameterValueCd,
TIFI.ProdFeeAmt,
TIFI.ProdFeeCurrCd,
TIFI.ProdFeePct,
TIFI.RangeFromDayCnt,
TIFI.RangeToDayCnt,
0,
0,
0,
0,
TIFI.ProdFeeLevelMinAmt,
TIFI.ProdFeeLevelMaxAmt,
0,
@UserId,
GETDATE(),
@UserId,
GETDATE(),
@FunctionId
FROM #tblContractInterCulturalDataFilteredInsert(NOLOCK) TIFI
END
IF(@ConvRateFlag=1)
BEGIN
EXEC [ASSET_DB].[dbo].[prcCalculateRates] @UserId,@ContractId,@SelectAll,@SubProductList,@FeesOverrideFlg
END
COMMIT TRANSACTION
SELECT @RetVal=0
GOTO CLEARTEMPTABLES
GOTO DONE
END TRY
BEGIN CATCH
SELECT @ErrMsg = ERROR_PROCEDURE() + ': ' + ERROR_MESSAGE() + ' (Error No. ' + Ltrim(Str(ERROR_NUMBER())) + ') ' + ' at line # ' + Ltrim(Str(ERROR_LINE()))
,@RetVal = 200
GOTO CLEARTEMPTABLES
GOTO ERROR
END CATCH
ERROR:
ROLLBACK TRANSACTION
RAISERROR(@ErrMsg,16,1 )
CLEARTEMPTABLES:
BEGIN TRY
IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#tblMasterData]') )
BEGIN
TRUNCATE TABLE #tblMasterData
DROP TABLE #tblMasterData
END
IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#tblContractInterCulturalDataFiltered]') )
BEGIN
TRUNCATE TABLE #tblContractInterCulturalDataFiltered
DROP TABLE #tblContractInterCulturalDataFiltered
END
IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#tblContractInterCulturalDataFilteredInsert]') )
BEGIN
TRUNCATE TABLE #tblContractInterCulturalDataFilteredInsert
DROP TABLE #tblContractInterCulturalDataFilteredInsert
END
IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#tblContractInterCulturalDataFilteredUpdate]') )
BEGIN
TRUNCATE TABLE #tblContractInterCulturalDataFilteredUpdate
DROP TABLE #tblContractInterCulturalDataFilteredUpdate
END
IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#tblSelContractSubProds]') )
BEGIN
TRUNCATE TABLE #tblSelContractSubProds
DROP TABLE #tblSelContractSubProds
END
END TRY
BEGIN CATCH
SELECT @ErrMsg = ERROR_PROCEDURE() + ': ' + ERROR_MESSAGE() + ' (Error No. ' + Ltrim(Str(ERROR_NUMBER())) + ') ' + ' at line # ' + Ltrim(Str(ERROR_LINE()))
,@RetVal = 201
GOTO ERROR
END CATCH
DONE:
SET NOCOUNT OFF
Return (@RetVal)
END
sql
sql-server tsql transactions edited Mar 8 at 7:17
marc_s
582k13011231269
asked Mar 8 at 6:41
Vishal I PatilVishal I Patil
1,24441734
add a comment |
0
In the below stored procedure, I'm getting the error mentioned in some cases, what am I doing wrong here?
Error:
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1
Stored procedure:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[prcInsDataFeesForVersion]
(@ContractId INT,
@UserId INT,
@VersionNo INT,
@SelectAll BIT,
@SubProductList NVARCHAR(MAX),
@ConvRateFlag BIT)
AS
BEGIN
SET NOCOUNT ON
DECLARE @RetVal INT,
@ErrMsg VARCHAR(255),
@FunctionId INT = 1234,
@AppId VARCHAR(20) = 'ID1021',
@FeesOverrideFlg BIT = 1,
@ContractProdFeeVerSummId INT
CREATE TABLE #tblMasterData
(
ProdCd INT,
ProdName NVARCHAR(255),
SubProdCd INT,
SubProdName NVARCHAR(255),
ProdTypeCd INT,
ProdDeliveryTypCd INT,
ProdStatusCd INT,
ProdStatusDt DATETIME,
CreatedDate DATETIME
)
CREATE TABLE #tblContractInterCulturalDataFiltered
(
ContractID INT
, ContractProdID INT
, ProdCd INT
, ProdName NVARCHAR(255)
, ContractSubProdID INT
, SubProdCd INT
, SubProdName NVARCHAR(255)
, ContractProdStatusCd INT
, ContractProdStatusDt DATETIME
, ContractSubProdStatusCd INT
, ContractSubProdStatusDt DATETIME
, ContractProdFeeId INT
, FeeTypCd INT
, RowMatrixParameterCd INT
, CreatedDate DATETIME
)
CREATE TABLE #tblContractInterCulturalDataFilteredInsert
(
ContractID INT
, ContractProdID INT
, ProdCd INT
, ProdName NVARCHAR(255)
, ContractSubProdID INT
, SubProdCd INT
, SubProdName NVARCHAR(255)
, ContractProdStatusCd INT
, ContractProdStatusDt DATETIME
, ContractSubProdStatusCd INT
, ContractSubProdStatusDt DATETIME
, FeeTypCd INT
, FeeDeterminantCd INT
, RowMatrixParameterCd INT
, ProdFeeCurrCd INT
, RowParameterValueCd INT
, ColumnParameterValueCd INT
, ProdFeePct REAL
, RangeFromDayCnt INT
, RangeToDayCnt INT
, ProdFeeLevelMinAmt DECIMAL
, ProdFeeLevelMaxAmt DECIMAL
, ProdFeeAmt DECIMAL
, ContractProdFeeId INT
, CreatedDate DATETIME
)
CREATE TABLE #tblContractInterCulturalDataFilteredUpdate
(
ContractID INT
, ContractProdID INT
, ProdCd INT
, ProdName NVARCHAR(255)
, ContractSubProdID INT
, SubProdCd INT
, SubProdName NVARCHAR(255)
, ContractProdStatusCd INT
, ContractProdStatusDt DATETIME
, ContractSubProdStatusCd INT
, ContractSubProdStatusDt DATETIME
, FeeTypCd INT
, FeeDeterminantCd INT
, RowMatrixParameterCd INT
, ProdFeeCurrCd INT
, RowParameterValueCd INT
, ColumnParameterValueCd INT
, ProdFeePct REAL
, RangeFromDayCnt INT
, RangeToDayCnt INT
, ProdFeeLevelMinAmt DECIMAL
, ProdFeeLevelMaxAmt DECIMAL
, ProdFeeAmt DECIMAL
, ContractProdFeeId INT
, CreatedDate DATETIME
)
CREATE TABLE #tblSelContractSubProds
(
ContractSubProdId INT
)
BEGIN TRY
BEGIN TRANSACTION
IF(@SelectAll=0 AND @SubProductList IS NOT NULL)
BEGIN
INSERT INTO #tblSelContractSubProds
SELECT intValue FROM dbo.udfSplit(@SubProductList,',',1)
END
INSERT INTO #tblMasterData(
ProdCd
, ProdName
, SubProdCd
, SubProdName
, ProdTypeCd
, ProdDeliveryTypCd
, ProdStatusCd
, ProdStatusDt
, CreatedDate
)
SELECT P.ProdCd
, PC.Descr
, S.SubProdCd
, SC.Descr
, P.ProdTypeCd
, P.ProdDeliveryTypCd
, P.ProdStatusCd
, P.ProdStatusDt
, GETDATE()
FROM tblProduct(NOLOCK) as P
INNER JOIN tblCode(NOLOCK) as PC ON P.ProdCd=PC.Cd and PC.Typ=1053
INNER JOIN tblSubProdDetail(NOLOCK) as S ON P.ProdCd=S.ProdCd
INNER JOIN tblCode(NOLOCK) as SC ON S.SubProdCd=SC.Cd and SC.Typ=1053
WHERE P.ProdDeliveryTypCd=3 and P.ProdStatusCd=1
IF(@SelectAll=1)
BEGIN
INSERT INTO #tblContractInterCulturalDataFiltered(
ContractID
, ContractProdID
, ProdCd
, ProdName
, ContractSubProdID
, SubProdCd
, SubProdName
, ContractProdStatusCd
, ContractProdStatusDt
, ContractSubProdStatusCd
, ContractSubProdStatusDt
, ContractProdFeeId
, FeeTypCd
, RowMatrixParameterCd
, CreatedDate
)
SELECT DISTINCT CP.ContractId,
CP.ContractProdId,
CP.ProdCd,
CPC.Descr,
CSP.ContractSubProdID,
CSP.SubProdCd,
CSPC.Descr,
CP.ContractProdStatusCd,
CP.ContractProdStatusDt,
CSP.ContractSubProdStatusCd,
CSP.ContractSubProdStatusDt,
CPF.ContractProdFeeId,
CPF.FeeTypCd,
CPF.RowMatrixParameterCd,
Getdate()
FROM #tblMasterData(NOLOCK) MD
INNER JOIN tblContractProd(NOLOCK) CP ON MD.ProdCd=CP.ProdCd
INNER JOIN tblContractSubProd(NOLOCK) CSP ON CP.ContractProdId=CSP.ContractProdId
INNER JOIN tblCode(NOLOCK) CPC ON CP.ProdCd=CPC.Cd and CPC.Typ=1053
INNER JOIN tblCode(NOLOCK) CSPC ON CSP.SubProdCd=CSPC.Cd and CSPC.Typ=1053
INNER JOIN tblContractProdFee(NOLOCK) CPF ON CP.ContractProdID=CPF.ContractProdId AND CSP.ContractSubProdID=CPF.ContractSubProdId
WHERE ContractId=@ContractId AND CP.ContractProdStatusCd=1 AND CSP.ContractSubProdStatusCd=1
AND CPF.ContractProdFeeTypStatusCd=1 AND CP.StandardProdIndCd=1 AND CSP.StdSubProdIndCd=1
END
ELSE
BEGIN
IF(@SubProductList IS NOT NULL)
BEGIN
INSERT INTO #tblContractInterCulturalDataFiltered(
ContractID
, ContractProdID
, ProdCd
, ProdName
, ContractSubProdID
, SubProdCd
, SubProdName
, ContractProdStatusCd
, ContractProdStatusDt
, ContractSubProdStatusCd
, ContractSubProdStatusDt
, ContractProdFeeId
, FeeTypCd
, RowMatrixParameterCd
, CreatedDate
)
SELECT DISTINCT CP.ContractId,
CP.ContractProdId,
CP.ProdCd,
CPC.Descr,
CSP.ContractSubProdID,
CSP.SubProdCd,
CSPC.Descr,
CP.ContractProdStatusCd,
CP.ContractProdStatusDt,
CSP.ContractSubProdStatusCd,
CSP.ContractSubProdStatusDt,
CPF.ContractProdFeeId,
CPF.FeeTypCd,
CPF.RowMatrixParameterCd,
Getdate()
FROM #tblMasterData(NOLOCK) MD
INNER JOIN tblContractProd(NOLOCK) CP ON MD.ProdCd=CP.ProdCd
INNER JOIN tblContractSubProd(NOLOCK) CSP ON CP.ContractProdId=CSP.ContractProdId
INNER JOIN tblCode(NOLOCK) CPC ON CP.ProdCd=CPC.Cd and CPC.Typ=1053
INNER JOIN tblCode(NOLOCK) CSPC ON CSP.SubProdCd=CSPC.Cd and CSPC.Typ=1053
INNER JOIN tblContractProdFee(NOLOCK) CPF ON CP.ContractProdID=CPF.ContractProdId AND CSP.ContractSubProdID=CPF.ContractSubProdId
INNER JOIN #tblSelContractSubProds(NOLOCK) SCSP ON CSP.ContractSubProdId=SCSP.ContractSubProdId
WHERE ContractId=@ContractId AND CP.ContractProdStatusCd=1 AND CSP.ContractSubProdStatusCd=1
AND CPF.ContractProdFeeTypStatusCd=1 AND CP.StandardProdIndCd=1 AND CSP.StdSubProdIndCd=1
END
END
INSERT INTO #tblContractInterCulturalDataFilteredUpdate(
ContractID
, ContractProdID
, ProdCd
, ProdName
, ContractSubProdID
, SubProdCd
, SubProdName
, ContractProdStatusCd
, ContractProdStatusDt
, ContractSubProdStatusCd
, ContractSubProdStatusDt
, FeeTypCd
, FeeDeterminantCd
, RowMatrixParameterCd
, ProdFeeCurrCd
, RowParameterValueCd
, ColumnParameterValueCd
, ProdFeePct
, RangeFromDayCnt
, RangeToDayCnt
, ProdFeeLevelMinAmt
, ProdFeeLevelMaxAmt
, ProdFeeAmt
, ContractProdFeeId
, CreatedDate
)
SELECT DISTINCT CICF.ContractId,
CICF.ContractProdId,
CICF.ProdCd,
CICF.ProdName,
CICF.ContractSubProdID,
CICF.SubProdCd,
CICF.SubProdName,
CICF.ContractProdStatusCd,
CICF.ContractProdStatusDt,
CICF.ContractSubProdStatusCd,
CICF.ContractSubProdStatusDt,
CPFM.FeeTypCd,
CPFM.FeeDeterminantCd,
CPFM.RowMatrixParameterCd,
CPFM.ProdFeeCurrCd,
CPFM.RowParameterValueCd,
CPFM.ColumnParameterValueCd,
CPFM.ProdFeePct,
CPFM.RangeFromDayCnt,
CPFM.RangeToDayCnt,
CPFM.ProdFeeLevelMinAmt,
CPFM.ProdFeeLevelMaxAmt,
CPFM.ProdFeeAmt,
CICF.ContractProdFeeId,
Getdate()
FROM #tblContractInterCulturalDataFiltered(NOLOCK) CICF
INNER JOIN tblContractProdFeeMatrix(NOLOCK) CPFM ON CICF.ProdCd=CPFM.FeeMatrixProdCd AND CICF.SubProdCd=CPFM.FeeMatrixSubProdCd AND CICF.FeeTypCd=CPFM.FeeTypCd
INNER JOIN tblContractProdFeeMatrixVersion(NOLOCK) CPFMV ON CPFM.ContractProdFeeMatrixVersionId=CPFMV.ContractProdFeeMatrixVersionId
INNER JOIN tblContractProdFee(NOLOCK) CP ON CICF.ContractProdID=CP.ContractProdId AND CICF.ContractSubProdID=CP.ContractSubProdId AND CICF.ContractProdFeeId=CP.ContractProdFeeId
AND CPFM.FeeDeterminantCd=CP.FeeDeterminantCd AND CPFM.RowMatrixParameterCd =CP.RowMatrixParameterCd
WHERE CICF.ContractId=@ContractId AND CPFMV.ProdFeeMatrixVerNo=@VersionNo AND CICF.ContractProdStatusCd=1 AND CICF.ContractSubProdStatusCd=1 AND CP.ContractProdFeeTypStatusCd=1
AND CPFM.RowMatrixParameterCd IN(6,4)
AND EXISTS(SELECT PFM.ContractProdFeeId from tblProdFeeMatrix(NOLOCK)PFM WHERE CICF.ContractProdFeeId=PFM.ContractProdFeeId)
IF EXISTS(Select 1 From #tblContractInterCulturalDataFilteredUpdate(NOLOCK))
BEGIN
DELETE PFM
FROM tblProdFeeMatrix(NOLOCK) PFM
INNER JOIN #tblContractInterCulturalDataFilteredUpdate(NOLOCK) TIFU ON TIFU.ContractProdFeeId=PFM.ContractProdFeeId
END
INSERT INTO #tblContractInterCulturalDataFilteredInsert(
ContractID
, ContractProdID
, ProdCd
, ProdName
, ContractSubProdID
, SubProdCd
, SubProdName
, ContractProdStatusCd
, ContractProdStatusDt
, ContractSubProdStatusCd
, ContractSubProdStatusDt
, FeeTypCd
, FeeDeterminantCd
, RowMatrixParameterCd
, ProdFeeCurrCd
, RowParameterValueCd
, ColumnParameterValueCd
, ProdFeePct
, RangeFromDayCnt
, RangeToDayCnt
, ProdFeeLevelMinAmt
, ProdFeeLevelMaxAmt
, ProdFeeAmt
, ContractProdFeeId
, CreatedDate
)
SELECT DISTINCT CICF.ContractId,
CICF.ContractProdId,
CICF.ProdCd,
CICF.ProdName,
CICF.ContractSubProdID,
CICF.SubProdCd,
CICF.SubProdName,
CICF.ContractProdStatusCd,
CICF.ContractProdStatusDt,
CICF.ContractSubProdStatusCd,
CICF.ContractSubProdStatusDt,
CPFM.FeeTypCd,
CPFM.FeeDeterminantCd,
CPFM.RowMatrixParameterCd,
CPFM.ProdFeeCurrCd,
CPFM.RowParameterValueCd,
CPFM.ColumnParameterValueCd,
CPFM.ProdFeePct,
CPFM.RangeFromDayCnt,
CPFM.RangeToDayCnt,
CPFM.ProdFeeLevelMinAmt,
CPFM.ProdFeeLevelMaxAmt,
CPFM.ProdFeeAmt,
CICF.ContractProdFeeId,
Getdate()
FROM #tblContractInterCulturalDataFiltered(NOLOCK) CICF
INNER JOIN tblContractProdFeeMatrix(NOLOCK) CPFM ON CICF.ProdCd=CPFM.FeeMatrixProdCd AND CICF.SubProdCd=CPFM.FeeMatrixSubProdCd AND CICF.FeeTypCd=CPFM.FeeTypCd
INNER JOIN tblContractProdFeeMatrixVersion(NOLOCK) CPFMV ON CPFM.ContractProdFeeMatrixVersionId=CPFMV.ContractProdFeeMatrixVersionId
INNER JOIN tblContractProdFee(NOLOCK) CP ON CICF.ContractProdID=CP.ContractProdId AND CICF.ContractSubProdID=CP.ContractSubProdId AND CICF.ContractProdFeeId=CP.ContractProdFeeId
AND CPFM.FeeDeterminantCd=CP.FeeDeterminantCd AND CPFM.RowMatrixParameterCd =CP.RowMatrixParameterCd
WHERE CICF.ContractId=@ContractId AND CPFMV.ProdFeeMatrixVerNo=@VersionNo AND CICF.ContractProdStatusCd=1 AND CICF.ContractSubProdStatusCd=1 AND CP.ContractProdFeeTypStatusCd=1
AND CPFM.RowMatrixParameterCd IN(6,4)
IF EXISTS(Select 1 From #tblContractInterCulturalDataFilteredInsert(NOLOCK))
BEGIN
INSERT INTO [dbo].[tblProdFeeMatrix]
([ContractProdFeeId]
,[ContractBundleFeeId]
,[ContractProdBundleAncilFeeId]
,[RowParameterValueCd]
,[ColumnParameterValueCd]
,[ProdFeeAmt]
,[ProdFeeCurrCd]
,[ProdFeePct]
,[RangeFromDayCnt]
,[RangeToDayCnt]
,[RangeFromPct]
,[RangeToPct]
,[RangeFromAmt]
,[RangeToAmt]
,[ProdFeeLevelMinAmt]
,[ProdFeeLevelMaxAmt]
,[FeeNotApplyIndCd]
,[CreateId]
,[CreateDt]
,[UpdateId]
,[UpdateDt]
,[FunctionId])
SELECT DISTINCT
TIFI.ContractProdFeeID,
0,
0,
TIFI.RowParameterValueCd,
TIFI.ColumnParameterValueCd,
TIFI.ProdFeeAmt,
TIFI.ProdFeeCurrCd,
TIFI.ProdFeePct,
TIFI.RangeFromDayCnt,
TIFI.RangeToDayCnt,
0,
0,
0,
0,
TIFI.ProdFeeLevelMinAmt,
TIFI.ProdFeeLevelMaxAmt,
0,
@UserId,
GETDATE(),
@UserId,
GETDATE(),
@FunctionId
FROM #tblContractInterCulturalDataFilteredInsert(NOLOCK) TIFI
END
IF(@ConvRateFlag=1)
BEGIN
EXEC [ASSET_DB].[dbo].[prcCalculateRates] @UserId,@ContractId,@SelectAll,@SubProductList,@FeesOverrideFlg
END
COMMIT TRANSACTION
SELECT @RetVal=0
GOTO CLEARTEMPTABLES
GOTO DONE
END TRY
BEGIN CATCH
SELECT @ErrMsg = ERROR_PROCEDURE() + ': ' + ERROR_MESSAGE() + ' (Error No. ' + Ltrim(Str(ERROR_NUMBER())) + ') ' + ' at line # ' + Ltrim(Str(ERROR_LINE()))
,@RetVal = 200
GOTO CLEARTEMPTABLES
GOTO ERROR
END CATCH
ERROR:
ROLLBACK TRANSACTION
RAISERROR(@ErrMsg,16,1 )
CLEARTEMPTABLES:
BEGIN TRY
IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#tblMasterData]') )
BEGIN
TRUNCATE TABLE #tblMasterData
DROP TABLE #tblMasterData
END
IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#tblContractInterCulturalDataFiltered]') )
BEGIN
TRUNCATE TABLE #tblContractInterCulturalDataFiltered
DROP TABLE #tblContractInterCulturalDataFiltered
END
IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#tblContractInterCulturalDataFilteredInsert]') )
BEGIN
TRUNCATE TABLE #tblContractInterCulturalDataFilteredInsert
DROP TABLE #tblContractInterCulturalDataFilteredInsert
END
IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#tblContractInterCulturalDataFilteredUpdate]') )
BEGIN
TRUNCATE TABLE #tblContractInterCulturalDataFilteredUpdate
DROP TABLE #tblContractInterCulturalDataFilteredUpdate
END
IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#tblSelContractSubProds]') )
BEGIN
TRUNCATE TABLE #tblSelContractSubProds
DROP TABLE #tblSelContractSubProds
END
END TRY
BEGIN CATCH
SELECT @ErrMsg = ERROR_PROCEDURE() + ': ' + ERROR_MESSAGE() + ' (Error No. ' + Ltrim(Str(ERROR_NUMBER())) + ') ' + ' at line # ' + Ltrim(Str(ERROR_LINE()))
,@RetVal = 201
GOTO ERROR
END CATCH
DONE:
SET NOCOUNT OFF
Return (@RetVal)
END
sql
sql-server tsql transactions edited Mar 8 at 7:17
marc_s
582k13011231269
asked Mar 8 at 6:41
Vishal I PatilVishal I Patil
1,24441734
Scattering NOLOCK hints in your code inside of a transaction is usually a sign of blind pattern usage. Highly doubtful that this hint is appropriate - especially when the queries are used to insert/update actual rows. The many uses of DISTINCT are also highly suspicious. Deleting from a table using the nolock hint is pointless, as is applying nolock hints to local temp tables. Truncating a table before dropping it makes no sense. Use of goto. <Big heavy sigh>. Get help!
– SMor
Mar 8 at 12:55
add a comment |
0
0
0
In the below stored procedure, I'm getting the error mentioned in some cases, what am I doing wrong here?
Error:
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1
Stored procedure:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[prcInsDataFeesForVersion]
(@ContractId INT,
@UserId INT,
@VersionNo INT,
@SelectAll BIT,
@SubProductList NVARCHAR(MAX),
@ConvRateFlag BIT)
AS
BEGIN
SET NOCOUNT ON
DECLARE @RetVal INT,
@ErrMsg VARCHAR(255),
@FunctionId INT = 1234,
@AppId VARCHAR(20) = 'ID1021',
@FeesOverrideFlg BIT = 1,
@ContractProdFeeVerSummId INT
CREATE TABLE #tblMasterData
(
ProdCd INT,
ProdName NVARCHAR(255),
SubProdCd INT,
SubProdName NVARCHAR(255),
ProdTypeCd INT,
ProdDeliveryTypCd INT,
ProdStatusCd INT,
ProdStatusDt DATETIME,
CreatedDate DATETIME
)
CREATE TABLE #tblContractInterCulturalDataFiltered
(
ContractID INT
, ContractProdID INT
, ProdCd INT
, ProdName NVARCHAR(255)
, ContractSubProdID INT
, SubProdCd INT
, SubProdName NVARCHAR(255)
, ContractProdStatusCd INT
, ContractProdStatusDt DATETIME
, ContractSubProdStatusCd INT
, ContractSubProdStatusDt DATETIME
, ContractProdFeeId INT
, FeeTypCd INT
, RowMatrixParameterCd INT
, CreatedDate DATETIME
)
CREATE TABLE #tblContractInterCulturalDataFilteredInsert
(
ContractID INT
, ContractProdID INT
, ProdCd INT
, ProdName NVARCHAR(255)
, ContractSubProdID INT
, SubProdCd INT
, SubProdName NVARCHAR(255)
, ContractProdStatusCd INT
, ContractProdStatusDt DATETIME
, ContractSubProdStatusCd INT
, ContractSubProdStatusDt DATETIME
, FeeTypCd INT
, FeeDeterminantCd INT
, RowMatrixParameterCd INT
, ProdFeeCurrCd INT
, RowParameterValueCd INT
, ColumnParameterValueCd INT
, ProdFeePct REAL
, RangeFromDayCnt INT
, RangeToDayCnt INT
, ProdFeeLevelMinAmt DECIMAL
, ProdFeeLevelMaxAmt DECIMAL
, ProdFeeAmt DECIMAL
, ContractProdFeeId INT
, CreatedDate DATETIME
)
CREATE TABLE #tblContractInterCulturalDataFilteredUpdate
(
ContractID INT
, ContractProdID INT
, ProdCd INT
, ProdName NVARCHAR(255)
, ContractSubProdID INT
, SubProdCd INT
, SubProdName NVARCHAR(255)
, ContractProdStatusCd INT
, ContractProdStatusDt DATETIME
, ContractSubProdStatusCd INT
, ContractSubProdStatusDt DATETIME
, FeeTypCd INT
, FeeDeterminantCd INT
, RowMatrixParameterCd INT
, ProdFeeCurrCd INT
, RowParameterValueCd INT
, ColumnParameterValueCd INT
, ProdFeePct REAL
, RangeFromDayCnt INT
, RangeToDayCnt INT
, ProdFeeLevelMinAmt DECIMAL
, ProdFeeLevelMaxAmt DECIMAL
, ProdFeeAmt DECIMAL
, ContractProdFeeId INT
, CreatedDate DATETIME
)
CREATE TABLE #tblSelContractSubProds
(
ContractSubProdId INT
)
BEGIN TRY
BEGIN TRANSACTION
IF(@SelectAll=0 AND @SubProductList IS NOT NULL)
BEGIN
INSERT INTO #tblSelContractSubProds
SELECT intValue FROM dbo.udfSplit(@SubProductList,',',1)
END
INSERT INTO #tblMasterData(
ProdCd
, ProdName
, SubProdCd
, SubProdName
, ProdTypeCd
, ProdDeliveryTypCd
, ProdStatusCd
, ProdStatusDt
, CreatedDate
)
SELECT P.ProdCd
, PC.Descr
, S.SubProdCd
, SC.Descr
, P.ProdTypeCd
, P.ProdDeliveryTypCd
, P.ProdStatusCd
, P.ProdStatusDt
, GETDATE()
FROM tblProduct(NOLOCK) as P
INNER JOIN tblCode(NOLOCK) as PC ON P.ProdCd=PC.Cd and PC.Typ=1053
INNER JOIN tblSubProdDetail(NOLOCK) as S ON P.ProdCd=S.ProdCd
INNER JOIN tblCode(NOLOCK) as SC ON S.SubProdCd=SC.Cd and SC.Typ=1053
WHERE P.ProdDeliveryTypCd=3 and P.ProdStatusCd=1
IF(@SelectAll=1)
BEGIN
INSERT INTO #tblContractInterCulturalDataFiltered(
ContractID
, ContractProdID
, ProdCd
, ProdName
, ContractSubProdID
, SubProdCd
, SubProdName
, ContractProdStatusCd
, ContractProdStatusDt
, ContractSubProdStatusCd
, ContractSubProdStatusDt
, ContractProdFeeId
, FeeTypCd
, RowMatrixParameterCd
, CreatedDate
)
SELECT DISTINCT CP.ContractId,
CP.ContractProdId,
CP.ProdCd,
CPC.Descr,
CSP.ContractSubProdID,
CSP.SubProdCd,
CSPC.Descr,
CP.ContractProdStatusCd,
CP.ContractProdStatusDt,
CSP.ContractSubProdStatusCd,
CSP.ContractSubProdStatusDt,
CPF.ContractProdFeeId,
CPF.FeeTypCd,
CPF.RowMatrixParameterCd,
Getdate()
FROM #tblMasterData(NOLOCK) MD
INNER JOIN tblContractProd(NOLOCK) CP ON MD.ProdCd=CP.ProdCd
INNER JOIN tblContractSubProd(NOLOCK) CSP ON CP.ContractProdId=CSP.ContractProdId
INNER JOIN tblCode(NOLOCK) CPC ON CP.ProdCd=CPC.Cd and CPC.Typ=1053
INNER JOIN tblCode(NOLOCK) CSPC ON CSP.SubProdCd=CSPC.Cd and CSPC.Typ=1053
INNER JOIN tblContractProdFee(NOLOCK) CPF ON CP.ContractProdID=CPF.ContractProdId AND CSP.ContractSubProdID=CPF.ContractSubProdId
WHERE ContractId=@ContractId AND CP.ContractProdStatusCd=1 AND CSP.ContractSubProdStatusCd=1
AND CPF.ContractProdFeeTypStatusCd=1 AND CP.StandardProdIndCd=1 AND CSP.StdSubProdIndCd=1
END
ELSE
BEGIN
IF(@SubProductList IS NOT NULL)
BEGIN
INSERT INTO #tblContractInterCulturalDataFiltered(
ContractID
, ContractProdID
, ProdCd
, ProdName
, ContractSubProdID
, SubProdCd
, SubProdName
, ContractProdStatusCd
, ContractProdStatusDt
, ContractSubProdStatusCd
, ContractSubProdStatusDt
, ContractProdFeeId
, FeeTypCd
, RowMatrixParameterCd
, CreatedDate
)
SELECT DISTINCT CP.ContractId,
CP.ContractProdId,
CP.ProdCd,
CPC.Descr,
CSP.ContractSubProdID,
CSP.SubProdCd,
CSPC.Descr,
CP.ContractProdStatusCd,
CP.ContractProdStatusDt,
CSP.ContractSubProdStatusCd,
CSP.ContractSubProdStatusDt,
CPF.ContractProdFeeId,
CPF.FeeTypCd,
CPF.RowMatrixParameterCd,
Getdate()
FROM #tblMasterData(NOLOCK) MD
INNER JOIN tblContractProd(NOLOCK) CP ON MD.ProdCd=CP.ProdCd
INNER JOIN tblContractSubProd(NOLOCK) CSP ON CP.ContractProdId=CSP.ContractProdId
INNER JOIN tblCode(NOLOCK) CPC ON CP.ProdCd=CPC.Cd and CPC.Typ=1053
INNER JOIN tblCode(NOLOCK) CSPC ON CSP.SubProdCd=CSPC.Cd and CSPC.Typ=1053
INNER JOIN tblContractProdFee(NOLOCK) CPF ON CP.ContractProdID=CPF.ContractProdId AND CSP.ContractSubProdID=CPF.ContractSubProdId
INNER JOIN #tblSelContractSubProds(NOLOCK) SCSP ON CSP.ContractSubProdId=SCSP.ContractSubProdId
WHERE ContractId=@ContractId AND CP.ContractProdStatusCd=1 AND CSP.ContractSubProdStatusCd=1
AND CPF.ContractProdFeeTypStatusCd=1 AND CP.StandardProdIndCd=1 AND CSP.StdSubProdIndCd=1
END
END
INSERT INTO #tblContractInterCulturalDataFilteredUpdate(
ContractID
, ContractProdID
, ProdCd
, ProdName
, ContractSubProdID
, SubProdCd
, SubProdName
, ContractProdStatusCd
, ContractProdStatusDt
, ContractSubProdStatusCd
, ContractSubProdStatusDt
, FeeTypCd
, FeeDeterminantCd
, RowMatrixParameterCd
, ProdFeeCurrCd
, RowParameterValueCd
, ColumnParameterValueCd
, ProdFeePct
, RangeFromDayCnt
, RangeToDayCnt
, ProdFeeLevelMinAmt
, ProdFeeLevelMaxAmt
, ProdFeeAmt
, ContractProdFeeId
, CreatedDate
)
SELECT DISTINCT CICF.ContractId,
CICF.ContractProdId,
CICF.ProdCd,
CICF.ProdName,
CICF.ContractSubProdID,
CICF.SubProdCd,
CICF.SubProdName,
CICF.ContractProdStatusCd,
CICF.ContractProdStatusDt,
CICF.ContractSubProdStatusCd,
CICF.ContractSubProdStatusDt,
CPFM.FeeTypCd,
CPFM.FeeDeterminantCd,
CPFM.RowMatrixParameterCd,
CPFM.ProdFeeCurrCd,
CPFM.RowParameterValueCd,
CPFM.ColumnParameterValueCd,
CPFM.ProdFeePct,
CPFM.RangeFromDayCnt,
CPFM.RangeToDayCnt,
CPFM.ProdFeeLevelMinAmt,
CPFM.ProdFeeLevelMaxAmt,
CPFM.ProdFeeAmt,
CICF.ContractProdFeeId,
Getdate()
FROM #tblContractInterCulturalDataFiltered(NOLOCK) CICF
INNER JOIN tblContractProdFeeMatrix(NOLOCK) CPFM ON CICF.ProdCd=CPFM.FeeMatrixProdCd AND CICF.SubProdCd=CPFM.FeeMatrixSubProdCd AND CICF.FeeTypCd=CPFM.FeeTypCd
INNER JOIN tblContractProdFeeMatrixVersion(NOLOCK) CPFMV ON CPFM.ContractProdFeeMatrixVersionId=CPFMV.ContractProdFeeMatrixVersionId
INNER JOIN tblContractProdFee(NOLOCK) CP ON CICF.ContractProdID=CP.ContractProdId AND CICF.ContractSubProdID=CP.ContractSubProdId AND CICF.ContractProdFeeId=CP.ContractProdFeeId
AND CPFM.FeeDeterminantCd=CP.FeeDeterminantCd AND CPFM.RowMatrixParameterCd =CP.RowMatrixParameterCd
WHERE CICF.ContractId=@ContractId AND CPFMV.ProdFeeMatrixVerNo=@VersionNo AND CICF.ContractProdStatusCd=1 AND CICF.ContractSubProdStatusCd=1 AND CP.ContractProdFeeTypStatusCd=1
AND CPFM.RowMatrixParameterCd IN(6,4)
AND EXISTS(SELECT PFM.ContractProdFeeId from tblProdFeeMatrix(NOLOCK)PFM WHERE CICF.ContractProdFeeId=PFM.ContractProdFeeId)
IF EXISTS(Select 1 From #tblContractInterCulturalDataFilteredUpdate(NOLOCK))
BEGIN
DELETE PFM
FROM tblProdFeeMatrix(NOLOCK) PFM
INNER JOIN #tblContractInterCulturalDataFilteredUpdate(NOLOCK) TIFU ON TIFU.ContractProdFeeId=PFM.ContractProdFeeId
END
INSERT INTO #tblContractInterCulturalDataFilteredInsert(
ContractID
, ContractProdID
, ProdCd
, ProdName
, ContractSubProdID
, SubProdCd
, SubProdName
, ContractProdStatusCd
, ContractProdStatusDt
, ContractSubProdStatusCd
, ContractSubProdStatusDt
, FeeTypCd
, FeeDeterminantCd
, RowMatrixParameterCd
, ProdFeeCurrCd
, RowParameterValueCd
, ColumnParameterValueCd
, ProdFeePct
, RangeFromDayCnt
, RangeToDayCnt
, ProdFeeLevelMinAmt
, ProdFeeLevelMaxAmt
, ProdFeeAmt
, ContractProdFeeId
, CreatedDate
)
SELECT DISTINCT CICF.ContractId,
CICF.ContractProdId,
CICF.ProdCd,
CICF.ProdName,
CICF.ContractSubProdID,
CICF.SubProdCd,
CICF.SubProdName,
CICF.ContractProdStatusCd,
CICF.ContractProdStatusDt,
CICF.ContractSubProdStatusCd,
CICF.ContractSubProdStatusDt,
CPFM.FeeTypCd,
CPFM.FeeDeterminantCd,
CPFM.RowMatrixParameterCd,
CPFM.ProdFeeCurrCd,
CPFM.RowParameterValueCd,
CPFM.ColumnParameterValueCd,
CPFM.ProdFeePct,
CPFM.RangeFromDayCnt,
CPFM.RangeToDayCnt,
CPFM.ProdFeeLevelMinAmt,
CPFM.ProdFeeLevelMaxAmt,
CPFM.ProdFeeAmt,
CICF.ContractProdFeeId,
Getdate()
FROM #tblContractInterCulturalDataFiltered(NOLOCK) CICF
INNER JOIN tblContractProdFeeMatrix(NOLOCK) CPFM ON CICF.ProdCd=CPFM.FeeMatrixProdCd AND CICF.SubProdCd=CPFM.FeeMatrixSubProdCd AND CICF.FeeTypCd=CPFM.FeeTypCd
INNER JOIN tblContractProdFeeMatrixVersion(NOLOCK) CPFMV ON CPFM.ContractProdFeeMatrixVersionId=CPFMV.ContractProdFeeMatrixVersionId
INNER JOIN tblContractProdFee(NOLOCK) CP ON CICF.ContractProdID=CP.ContractProdId AND CICF.ContractSubProdID=CP.ContractSubProdId AND CICF.ContractProdFeeId=CP.ContractProdFeeId
AND CPFM.FeeDeterminantCd=CP.FeeDeterminantCd AND CPFM.RowMatrixParameterCd =CP.RowMatrixParameterCd
WHERE CICF.ContractId=@ContractId AND CPFMV.ProdFeeMatrixVerNo=@VersionNo AND CICF.ContractProdStatusCd=1 AND CICF.ContractSubProdStatusCd=1 AND CP.ContractProdFeeTypStatusCd=1
AND CPFM.RowMatrixParameterCd IN(6,4)
IF EXISTS(Select 1 From #tblContractInterCulturalDataFilteredInsert(NOLOCK))
BEGIN
INSERT INTO [dbo].[tblProdFeeMatrix]
([ContractProdFeeId]
,[ContractBundleFeeId]
,[ContractProdBundleAncilFeeId]
,[RowParameterValueCd]
,[ColumnParameterValueCd]
,[ProdFeeAmt]
,[ProdFeeCurrCd]
,[ProdFeePct]
,[RangeFromDayCnt]
,[RangeToDayCnt]
,[RangeFromPct]
,[RangeToPct]
,[RangeFromAmt]
,[RangeToAmt]
,[ProdFeeLevelMinAmt]
,[ProdFeeLevelMaxAmt]
,[FeeNotApplyIndCd]
,[CreateId]
,[CreateDt]
,[UpdateId]
,[UpdateDt]
,[FunctionId])
SELECT DISTINCT
TIFI.ContractProdFeeID,
0,
0,
TIFI.RowParameterValueCd,
TIFI.ColumnParameterValueCd,
TIFI.ProdFeeAmt,
TIFI.ProdFeeCurrCd,
TIFI.ProdFeePct,
TIFI.RangeFromDayCnt,
TIFI.RangeToDayCnt,
0,
0,
0,
0,
TIFI.ProdFeeLevelMinAmt,
TIFI.ProdFeeLevelMaxAmt,
0,
@UserId,
GETDATE(),
@UserId,
GETDATE(),
@FunctionId
FROM #tblContractInterCulturalDataFilteredInsert(NOLOCK) TIFI
END
IF(@ConvRateFlag=1)
BEGIN
EXEC [ASSET_DB].[dbo].[prcCalculateRates] @UserId,@ContractId,@SelectAll,@SubProductList,@FeesOverrideFlg
END
COMMIT TRANSACTION
SELECT @RetVal=0
GOTO CLEARTEMPTABLES
GOTO DONE
END TRY
BEGIN CATCH
SELECT @ErrMsg = ERROR_PROCEDURE() + ': ' + ERROR_MESSAGE() + ' (Error No. ' + Ltrim(Str(ERROR_NUMBER())) + ') ' + ' at line # ' + Ltrim(Str(ERROR_LINE()))
,@RetVal = 200
GOTO CLEARTEMPTABLES
GOTO ERROR
END CATCH
ERROR:
ROLLBACK TRANSACTION
RAISERROR(@ErrMsg,16,1 )
CLEARTEMPTABLES:
BEGIN TRY
IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#tblMasterData]') )
BEGIN
TRUNCATE TABLE #tblMasterData
DROP TABLE #tblMasterData
END
IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#tblContractInterCulturalDataFiltered]') )
BEGIN
TRUNCATE TABLE #tblContractInterCulturalDataFiltered
DROP TABLE #tblContractInterCulturalDataFiltered
END
IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#tblContractInterCulturalDataFilteredInsert]') )
BEGIN
TRUNCATE TABLE #tblContractInterCulturalDataFilteredInsert
DROP TABLE #tblContractInterCulturalDataFilteredInsert
END
IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#tblContractInterCulturalDataFilteredUpdate]') )
BEGIN
TRUNCATE TABLE #tblContractInterCulturalDataFilteredUpdate
DROP TABLE #tblContractInterCulturalDataFilteredUpdate
END
IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#tblSelContractSubProds]') )
BEGIN
TRUNCATE TABLE #tblSelContractSubProds
DROP TABLE #tblSelContractSubProds
END
END TRY
BEGIN CATCH
SELECT @ErrMsg = ERROR_PROCEDURE() + ': ' + ERROR_MESSAGE() + ' (Error No. ' + Ltrim(Str(ERROR_NUMBER())) + ') ' + ' at line # ' + Ltrim(Str(ERROR_LINE()))
,@RetVal = 201
GOTO ERROR
END CATCH
DONE:
SET NOCOUNT OFF
Return (@RetVal)
END
sql
sql-server tsql transactions edited Mar 8 at 7:17
marc_s
582k13011231269
asked Mar 8 at 6:41
Vishal I PatilVishal I Patil
1,24441734
In the below stored procedure, I'm getting the error mentioned in some cases, what am I doing wrong here?
Error:
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1
Stored procedure:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[prcInsDataFeesForVersion]
(@ContractId INT,
@UserId INT,
@VersionNo INT,
@SelectAll BIT,
@SubProductList NVARCHAR(MAX),
@ConvRateFlag BIT)
AS
BEGIN
SET NOCOUNT ON
DECLARE @RetVal INT,
@ErrMsg VARCHAR(255),
@FunctionId INT = 1234,
@AppId VARCHAR(20) = 'ID1021',
@FeesOverrideFlg BIT = 1,
@ContractProdFeeVerSummId INT
CREATE TABLE #tblMasterData
(
ProdCd INT,
ProdName NVARCHAR(255),
SubProdCd INT,
SubProdName NVARCHAR(255),
ProdTypeCd INT,
ProdDeliveryTypCd INT,
ProdStatusCd INT,
ProdStatusDt DATETIME,
CreatedDate DATETIME
)
CREATE TABLE #tblContractInterCulturalDataFiltered
(
ContractID INT
, ContractProdID INT
, ProdCd INT
, ProdName NVARCHAR(255)
, ContractSubProdID INT
, SubProdCd INT
, SubProdName NVARCHAR(255)
, ContractProdStatusCd INT
, ContractProdStatusDt DATETIME
, ContractSubProdStatusCd INT
, ContractSubProdStatusDt DATETIME
, ContractProdFeeId INT
, FeeTypCd INT
, RowMatrixParameterCd INT
, CreatedDate DATETIME
)
CREATE TABLE #tblContractInterCulturalDataFilteredInsert
(
ContractID INT
, ContractProdID INT
, ProdCd INT
, ProdName NVARCHAR(255)
, ContractSubProdID INT
, SubProdCd INT
, SubProdName NVARCHAR(255)
, ContractProdStatusCd INT
, ContractProdStatusDt DATETIME
, ContractSubProdStatusCd INT
, ContractSubProdStatusDt DATETIME
, FeeTypCd INT
, FeeDeterminantCd INT
, RowMatrixParameterCd INT
, ProdFeeCurrCd INT
, RowParameterValueCd INT
, ColumnParameterValueCd INT
, ProdFeePct REAL
, RangeFromDayCnt INT
, RangeToDayCnt INT
, ProdFeeLevelMinAmt DECIMAL
, ProdFeeLevelMaxAmt DECIMAL
, ProdFeeAmt DECIMAL
, ContractProdFeeId INT
, CreatedDate DATETIME
)
CREATE TABLE #tblContractInterCulturalDataFilteredUpdate
(
ContractID INT
, ContractProdID INT
, ProdCd INT
, ProdName NVARCHAR(255)
, ContractSubProdID INT
, SubProdCd INT
, SubProdName NVARCHAR(255)
, ContractProdStatusCd INT
, ContractProdStatusDt DATETIME
, ContractSubProdStatusCd INT
, ContractSubProdStatusDt DATETIME
, FeeTypCd INT
, FeeDeterminantCd INT
, RowMatrixParameterCd INT
, ProdFeeCurrCd INT
, RowParameterValueCd INT
, ColumnParameterValueCd INT
, ProdFeePct REAL
, RangeFromDayCnt INT
, RangeToDayCnt INT
, ProdFeeLevelMinAmt DECIMAL
, ProdFeeLevelMaxAmt DECIMAL
, ProdFeeAmt DECIMAL
, ContractProdFeeId INT
, CreatedDate DATETIME
)
CREATE TABLE #tblSelContractSubProds
(
ContractSubProdId INT
)
BEGIN TRY
BEGIN TRANSACTION
IF(@SelectAll=0 AND @SubProductList IS NOT NULL)
BEGIN
INSERT INTO #tblSelContractSubProds
SELECT intValue FROM dbo.udfSplit(@SubProductList,',',1)
END
INSERT INTO #tblMasterData(
ProdCd
, ProdName
, SubProdCd
, SubProdName
, ProdTypeCd
, ProdDeliveryTypCd
, ProdStatusCd
, ProdStatusDt
, CreatedDate
)
SELECT P.ProdCd
, PC.Descr
, S.SubProdCd
, SC.Descr
, P.ProdTypeCd
, P.ProdDeliveryTypCd
, P.ProdStatusCd
, P.ProdStatusDt
, GETDATE()
FROM tblProduct(NOLOCK) as P
INNER JOIN tblCode(NOLOCK) as PC ON P.ProdCd=PC.Cd and PC.Typ=1053
INNER JOIN tblSubProdDetail(NOLOCK) as S ON P.ProdCd=S.ProdCd
INNER JOIN tblCode(NOLOCK) as SC ON S.SubProdCd=SC.Cd and SC.Typ=1053
WHERE P.ProdDeliveryTypCd=3 and P.ProdStatusCd=1
IF(@SelectAll=1)
BEGIN
INSERT INTO #tblContractInterCulturalDataFiltered(
ContractID
, ContractProdID
, ProdCd
, ProdName
, ContractSubProdID
, SubProdCd
, SubProdName
, ContractProdStatusCd
, ContractProdStatusDt
, ContractSubProdStatusCd
, ContractSubProdStatusDt
, ContractProdFeeId
, FeeTypCd
, RowMatrixParameterCd
, CreatedDate
)
SELECT DISTINCT CP.ContractId,
CP.ContractProdId,
CP.ProdCd,
CPC.Descr,
CSP.ContractSubProdID,
CSP.SubProdCd,
CSPC.Descr,
CP.ContractProdStatusCd,
CP.ContractProdStatusDt,
CSP.ContractSubProdStatusCd,
CSP.ContractSubProdStatusDt,
CPF.ContractProdFeeId,
CPF.FeeTypCd,
CPF.RowMatrixParameterCd,
Getdate()
FROM #tblMasterData(NOLOCK) MD
INNER JOIN tblContractProd(NOLOCK) CP ON MD.ProdCd=CP.ProdCd
INNER JOIN tblContractSubProd(NOLOCK) CSP ON CP.ContractProdId=CSP.ContractProdId
INNER JOIN tblCode(NOLOCK) CPC ON CP.ProdCd=CPC.Cd and CPC.Typ=1053
INNER JOIN tblCode(NOLOCK) CSPC ON CSP.SubProdCd=CSPC.Cd and CSPC.Typ=1053
INNER JOIN tblContractProdFee(NOLOCK) CPF ON CP.ContractProdID=CPF.ContractProdId AND CSP.ContractSubProdID=CPF.ContractSubProdId
WHERE ContractId=@ContractId AND CP.ContractProdStatusCd=1 AND CSP.ContractSubProdStatusCd=1
AND CPF.ContractProdFeeTypStatusCd=1 AND CP.StandardProdIndCd=1 AND CSP.StdSubProdIndCd=1
END
ELSE
BEGIN
IF(@SubProductList IS NOT NULL)
BEGIN
INSERT INTO #tblContractInterCulturalDataFiltered(
ContractID
, ContractProdID
, ProdCd
, ProdName
, ContractSubProdID
, SubProdCd
, SubProdName
, ContractProdStatusCd
, ContractProdStatusDt
, ContractSubProdStatusCd
, ContractSubProdStatusDt
, ContractProdFeeId
, FeeTypCd
, RowMatrixParameterCd
, CreatedDate
)
SELECT DISTINCT CP.ContractId,
CP.ContractProdId,
CP.ProdCd,
CPC.Descr,
CSP.ContractSubProdID,
CSP.SubProdCd,
CSPC.Descr,
CP.ContractProdStatusCd,
CP.ContractProdStatusDt,
CSP.ContractSubProdStatusCd,
CSP.ContractSubProdStatusDt,
CPF.ContractProdFeeId,
CPF.FeeTypCd,
CPF.RowMatrixParameterCd,
Getdate()
FROM #tblMasterData(NOLOCK) MD
INNER JOIN tblContractProd(NOLOCK) CP ON MD.ProdCd=CP.ProdCd
INNER JOIN tblContractSubProd(NOLOCK) CSP ON CP.ContractProdId=CSP.ContractProdId
INNER JOIN tblCode(NOLOCK) CPC ON CP.ProdCd=CPC.Cd and CPC.Typ=1053
INNER JOIN tblCode(NOLOCK) CSPC ON CSP.SubProdCd=CSPC.Cd and CSPC.Typ=1053
INNER JOIN tblContractProdFee(NOLOCK) CPF ON CP.ContractProdID=CPF.ContractProdId AND CSP.ContractSubProdID=CPF.ContractSubProdId
INNER JOIN #tblSelContractSubProds(NOLOCK) SCSP ON CSP.ContractSubProdId=SCSP.ContractSubProdId
WHERE ContractId=@ContractId AND CP.ContractProdStatusCd=1 AND CSP.ContractSubProdStatusCd=1
AND CPF.ContractProdFeeTypStatusCd=1 AND CP.StandardProdIndCd=1 AND CSP.StdSubProdIndCd=1
END
END
INSERT INTO #tblContractInterCulturalDataFilteredUpdate(
ContractID
, ContractProdID
, ProdCd
, ProdName
, ContractSubProdID
, SubProdCd
, SubProdName
, ContractProdStatusCd
, ContractProdStatusDt
, ContractSubProdStatusCd
, ContractSubProdStatusDt
, FeeTypCd
, FeeDeterminantCd
, RowMatrixParameterCd
, ProdFeeCurrCd
, RowParameterValueCd
, ColumnParameterValueCd
, ProdFeePct
, RangeFromDayCnt
, RangeToDayCnt
, ProdFeeLevelMinAmt
, ProdFeeLevelMaxAmt
, ProdFeeAmt
, ContractProdFeeId
, CreatedDate
)
SELECT DISTINCT CICF.ContractId,
CICF.ContractProdId,
CICF.ProdCd,
CICF.ProdName,
CICF.ContractSubProdID,
CICF.SubProdCd,
CICF.SubProdName,
CICF.ContractProdStatusCd,
CICF.ContractProdStatusDt,
CICF.ContractSubProdStatusCd,
CICF.ContractSubProdStatusDt,
CPFM.FeeTypCd,
CPFM.FeeDeterminantCd,
CPFM.RowMatrixParameterCd,
CPFM.ProdFeeCurrCd,
CPFM.RowParameterValueCd,
CPFM.ColumnParameterValueCd,
CPFM.ProdFeePct,
CPFM.RangeFromDayCnt,
CPFM.RangeToDayCnt,
CPFM.ProdFeeLevelMinAmt,
CPFM.ProdFeeLevelMaxAmt,
CPFM.ProdFeeAmt,
CICF.ContractProdFeeId,
Getdate()
FROM #tblContractInterCulturalDataFiltered(NOLOCK) CICF
INNER JOIN tblContractProdFeeMatrix(NOLOCK) CPFM ON CICF.ProdCd=CPFM.FeeMatrixProdCd AND CICF.SubProdCd=CPFM.FeeMatrixSubProdCd AND CICF.FeeTypCd=CPFM.FeeTypCd
INNER JOIN tblContractProdFeeMatrixVersion(NOLOCK) CPFMV ON CPFM.ContractProdFeeMatrixVersionId=CPFMV.ContractProdFeeMatrixVersionId
INNER JOIN tblContractProdFee(NOLOCK) CP ON CICF.ContractProdID=CP.ContractProdId AND CICF.ContractSubProdID=CP.ContractSubProdId AND CICF.ContractProdFeeId=CP.ContractProdFeeId
AND CPFM.FeeDeterminantCd=CP.FeeDeterminantCd AND CPFM.RowMatrixParameterCd =CP.RowMatrixParameterCd
WHERE CICF.ContractId=@ContractId AND CPFMV.ProdFeeMatrixVerNo=@VersionNo AND CICF.ContractProdStatusCd=1 AND CICF.ContractSubProdStatusCd=1 AND CP.ContractProdFeeTypStatusCd=1
AND CPFM.RowMatrixParameterCd IN(6,4)
AND EXISTS(SELECT PFM.ContractProdFeeId from tblProdFeeMatrix(NOLOCK)PFM WHERE CICF.ContractProdFeeId=PFM.ContractProdFeeId)
IF EXISTS(Select 1 From #tblContractInterCulturalDataFilteredUpdate(NOLOCK))
BEGIN
DELETE PFM
FROM tblProdFeeMatrix(NOLOCK) PFM
INNER JOIN #tblContractInterCulturalDataFilteredUpdate(NOLOCK) TIFU ON TIFU.ContractProdFeeId=PFM.ContractProdFeeId
END
INSERT INTO #tblContractInterCulturalDataFilteredInsert(
ContractID
, ContractProdID
, ProdCd
, ProdName
, ContractSubProdID
, SubProdCd
, SubProdName
, ContractProdStatusCd
, ContractProdStatusDt
, ContractSubProdStatusCd
, ContractSubProdStatusDt
, FeeTypCd
, FeeDeterminantCd
, RowMatrixParameterCd
, ProdFeeCurrCd
, RowParameterValueCd
, ColumnParameterValueCd
, ProdFeePct
, RangeFromDayCnt
, RangeToDayCnt
, ProdFeeLevelMinAmt
, ProdFeeLevelMaxAmt
, ProdFeeAmt
, ContractProdFeeId
, CreatedDate
)
SELECT DISTINCT CICF.ContractId,
CICF.ContractProdId,
CICF.ProdCd,
CICF.ProdName,
CICF.ContractSubProdID,
CICF.SubProdCd,
CICF.SubProdName,
CICF.ContractProdStatusCd,
CICF.ContractProdStatusDt,
CICF.ContractSubProdStatusCd,
CICF.ContractSubProdStatusDt,
CPFM.FeeTypCd,
CPFM.FeeDeterminantCd,
CPFM.RowMatrixParameterCd,
CPFM.ProdFeeCurrCd,
CPFM.RowParameterValueCd,
CPFM.ColumnParameterValueCd,
CPFM.ProdFeePct,
CPFM.RangeFromDayCnt,
CPFM.RangeToDayCnt,
CPFM.ProdFeeLevelMinAmt,
CPFM.ProdFeeLevelMaxAmt,
CPFM.ProdFeeAmt,
CICF.ContractProdFeeId,
Getdate()
FROM #tblContractInterCulturalDataFiltered(NOLOCK) CICF
INNER JOIN tblContractProdFeeMatrix(NOLOCK) CPFM ON CICF.ProdCd=CPFM.FeeMatrixProdCd AND CICF.SubProdCd=CPFM.FeeMatrixSubProdCd AND CICF.FeeTypCd=CPFM.FeeTypCd
INNER JOIN tblContractProdFeeMatrixVersion(NOLOCK) CPFMV ON CPFM.ContractProdFeeMatrixVersionId=CPFMV.ContractProdFeeMatrixVersionId
INNER JOIN tblContractProdFee(NOLOCK) CP ON CICF.ContractProdID=CP.ContractProdId AND CICF.ContractSubProdID=CP.ContractSubProdId AND CICF.ContractProdFeeId=CP.ContractProdFeeId
AND CPFM.FeeDeterminantCd=CP.FeeDeterminantCd AND CPFM.RowMatrixParameterCd =CP.RowMatrixParameterCd
WHERE CICF.ContractId=@ContractId AND CPFMV.ProdFeeMatrixVerNo=@VersionNo AND CICF.ContractProdStatusCd=1 AND CICF.ContractSubProdStatusCd=1 AND CP.ContractProdFeeTypStatusCd=1
AND CPFM.RowMatrixParameterCd IN(6,4)
IF EXISTS(Select 1 From #tblContractInterCulturalDataFilteredInsert(NOLOCK))
BEGIN
INSERT INTO [dbo].[tblProdFeeMatrix]
([ContractProdFeeId]
,[ContractBundleFeeId]
,[ContractProdBundleAncilFeeId]
,[RowParameterValueCd]
,[ColumnParameterValueCd]
,[ProdFeeAmt]
,[ProdFeeCurrCd]
,[ProdFeePct]
,[RangeFromDayCnt]
,[RangeToDayCnt]
,[RangeFromPct]
,[RangeToPct]
,[RangeFromAmt]
,[RangeToAmt]
,[ProdFeeLevelMinAmt]
,[ProdFeeLevelMaxAmt]
,[FeeNotApplyIndCd]
,[CreateId]
,[CreateDt]
,[UpdateId]
,[UpdateDt]
,[FunctionId])
SELECT DISTINCT
TIFI.ContractProdFeeID,
0,
0,
TIFI.RowParameterValueCd,
TIFI.ColumnParameterValueCd,
TIFI.ProdFeeAmt,
TIFI.ProdFeeCurrCd,
TIFI.ProdFeePct,
TIFI.RangeFromDayCnt,
TIFI.RangeToDayCnt,
0,
0,
0,
0,
TIFI.ProdFeeLevelMinAmt,
TIFI.ProdFeeLevelMaxAmt,
0,
@UserId,
GETDATE(),
@UserId,
GETDATE(),
@FunctionId
FROM #tblContractInterCulturalDataFilteredInsert(NOLOCK) TIFI
END
IF(@ConvRateFlag=1)
BEGIN
EXEC [ASSET_DB].[dbo].[prcCalculateRates] @UserId,@ContractId,@SelectAll,@SubProductList,@FeesOverrideFlg
END
COMMIT TRANSACTION
SELECT @RetVal=0
GOTO CLEARTEMPTABLES
GOTO DONE
END TRY
BEGIN CATCH
SELECT @ErrMsg = ERROR_PROCEDURE() + ': ' + ERROR_MESSAGE() + ' (Error No. ' + Ltrim(Str(ERROR_NUMBER())) + ') ' + ' at line # ' + Ltrim(Str(ERROR_LINE()))
,@RetVal = 200
GOTO CLEARTEMPTABLES
GOTO ERROR
END CATCH
ERROR:
ROLLBACK TRANSACTION
RAISERROR(@ErrMsg,16,1 )
CLEARTEMPTABLES:
BEGIN TRY
IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#tblMasterData]') )
BEGIN
TRUNCATE TABLE #tblMasterData
DROP TABLE #tblMasterData
END
IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#tblContractInterCulturalDataFiltered]') )
BEGIN
TRUNCATE TABLE #tblContractInterCulturalDataFiltered
DROP TABLE #tblContractInterCulturalDataFiltered
END
IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#tblContractInterCulturalDataFilteredInsert]') )
BEGIN
TRUNCATE TABLE #tblContractInterCulturalDataFilteredInsert
DROP TABLE #tblContractInterCulturalDataFilteredInsert
END
IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#tblContractInterCulturalDataFilteredUpdate]') )
BEGIN
TRUNCATE TABLE #tblContractInterCulturalDataFilteredUpdate
DROP TABLE #tblContractInterCulturalDataFilteredUpdate
END
IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#tblSelContractSubProds]') )
BEGIN
TRUNCATE TABLE #tblSelContractSubProds
DROP TABLE #tblSelContractSubProds
END
END TRY
BEGIN CATCH
SELECT @ErrMsg = ERROR_PROCEDURE() + ': ' + ERROR_MESSAGE() + ' (Error No. ' + Ltrim(Str(ERROR_NUMBER())) + ') ' + ' at line # ' + Ltrim(Str(ERROR_LINE()))
,@RetVal = 201
GOTO ERROR
END CATCH
DONE:
SET NOCOUNT OFF
Return (@RetVal)
END
sql
sql-server tsql transactions sql
sql-server tsql transactions edited Mar 8 at 7:17
marc_s
582k13011231269
asked Mar 8 at 6:41
Vishal I PatilVishal I Patil
1,24441734
edited Mar 8 at 7:17
marc_s
582k13011231269
asked Mar 8 at 6:41
Vishal I PatilVishal I Patil
1,24441734
edited Mar 8 at 7:17
marc_s
582k13011231269
edited Mar 8 at 7:17
marc_s
582k13011231269
edited Mar 8 at 7:17
marc_s
582k13011231269
582k13011231269
asked Mar 8 at 6:41
Vishal I PatilVishal I Patil
1,24441734
asked Mar 8 at 6:41
Vishal I PatilVishal I Patil
1,24441734
asked Mar 8 at 6:41
Vishal I PatilVishal I Patil
1,24441734
1,24441734
Scattering NOLOCK hints in your code inside of a transaction is usually a sign of blind pattern usage. Highly doubtful that this hint is appropriate - especially when the queries are used to insert/update actual rows. The many uses of DISTINCT are also highly suspicious. Deleting from a table using the nolock hint is pointless, as is applying nolock hints to local temp tables. Truncating a table before dropping it makes no sense. Use of goto. <Big heavy sigh>. Get help!
– SMor
Mar 8 at 12:55
add a comment |
Scattering NOLOCK hints in your code inside of a transaction is usually a sign of blind pattern usage. Highly doubtful that this hint is appropriate - especially when the queries are used to insert/update actual rows. The many uses of DISTINCT are also highly suspicious. Deleting from a table using the nolock hint is pointless, as is applying nolock hints to local temp tables. Truncating a table before dropping it makes no sense. Use of goto. <Big heavy sigh>. Get help!
– SMor
Mar 8 at 12:55
Scattering NOLOCK hints in your code inside of a transaction is usually a sign of blind pattern usage. Highly doubtful that this hint is appropriate - especially when the queries are used to insert/update actual rows. The many uses of DISTINCT are also highly suspicious. Deleting from a table using the nolock hint is pointless, as is applying nolock hints to local temp tables. Truncating a table before dropping it makes no sense. Use of goto. <Big heavy sigh>. Get help!
– SMor
Mar 8 at 12:55
Scattering NOLOCK hints in your code inside of a transaction is usually a sign of blind pattern usage. Highly doubtful that this hint is appropriate - especially when the queries are used to insert/update actual rows. The many uses of DISTINCT are also highly suspicious. Deleting from a table using the nolock hint is pointless, as is applying nolock hints to local temp tables. Truncating a table before dropping it makes no sense. Use of goto. <Big heavy sigh>. Get help!
– SMor
Mar 8 at 12:55
add a comment |
1 Answer
1
active
oldest
votes
0
The fact that lead to this error is that the BEGIN TRANSACTION executed, but neither COMMIT or ROLLBACK did.
The usual reason a stored procedure with a try-transaction block returns that message is the error: table (or other object) not found. This is NOT caught in the try-catch block.
Check the object names exist on runtime.
Also, make sure to run a ROLLBACK manually on the session that spawned this message, if you still haven't fixed the proc.
Documentation:
The following types of errors are not handled by a CATCH block when
they occur at the same level of execution as the TRY...CATCH
construct:
(........)
Object name resolution errors
As you can see, there are also other errors not caught which may be the problem. Check out the possibilities. Many of these error, including the object name resolution, can be caught if you put the procedure CALL inside a try-catch block.
begin try
exec [dbo].[prcInsDataFeesForVersion] ..............
end try
begin catch
SELECT @ErrMsg = ERROR_PROCEDURE() + ': ' + ERROR_MESSAGE() + ' (Error No. ' + Ltrim(Str(ERROR_NUMBER())) + ') ' + ' at line # ' + Ltrim(Str(ERROR_LINE()))
,@RetVal = 201
end catch
This might not sound handy, but at least you can use it to find the error out.
answered Mar 8 at 6:54
George MenoutisGeorge Menoutis
2,833521
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55057994%2fsql-server-transaction-count-after-execute-indicates-a-mismatching-number-of-beg%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
The fact that lead to this error is that the BEGIN TRANSACTION executed, but neither COMMIT or ROLLBACK did.
The usual reason a stored procedure with a try-transaction block returns that message is the error: table (or other object) not found. This is NOT caught in the try-catch block.
Check the object names exist on runtime.
Also, make sure to run a ROLLBACK manually on the session that spawned this message, if you still haven't fixed the proc.
Documentation:
The following types of errors are not handled by a CATCH block when
they occur at the same level of execution as the TRY...CATCH
construct:
(........)
Object name resolution errors
As you can see, there are also other errors not caught which may be the problem. Check out the possibilities. Many of these error, including the object name resolution, can be caught if you put the procedure CALL inside a try-catch block.
begin try
exec [dbo].[prcInsDataFeesForVersion] ..............
end try
begin catch
SELECT @ErrMsg = ERROR_PROCEDURE() + ': ' + ERROR_MESSAGE() + ' (Error No. ' + Ltrim(Str(ERROR_NUMBER())) + ') ' + ' at line # ' + Ltrim(Str(ERROR_LINE()))
,@RetVal = 201
end catch
This might not sound handy, but at least you can use it to find the error out.
answered Mar 8 at 6:54
George MenoutisGeorge Menoutis
2,833521
add a comment |
0
The fact that lead to this error is that the BEGIN TRANSACTION executed, but neither COMMIT or ROLLBACK did.
The usual reason a stored procedure with a try-transaction block returns that message is the error: table (or other object) not found. This is NOT caught in the try-catch block.
Check the object names exist on runtime.
Also, make sure to run a ROLLBACK manually on the session that spawned this message, if you still haven't fixed the proc.
Documentation:
The following types of errors are not handled by a CATCH block when
they occur at the same level of execution as the TRY...CATCH
construct:
(........)
Object name resolution errors
As you can see, there are also other errors not caught which may be the problem. Check out the possibilities. Many of these error, including the object name resolution, can be caught if you put the procedure CALL inside a try-catch block.
begin try
exec [dbo].[prcInsDataFeesForVersion] ..............
end try
begin catch
SELECT @ErrMsg = ERROR_PROCEDURE() + ': ' + ERROR_MESSAGE() + ' (Error No. ' + Ltrim(Str(ERROR_NUMBER())) + ') ' + ' at line # ' + Ltrim(Str(ERROR_LINE()))
,@RetVal = 201
end catch
This might not sound handy, but at least you can use it to find the error out.
answered Mar 8 at 6:54
George MenoutisGeorge Menoutis
2,833521
add a comment |
0
0
0
The fact that lead to this error is that the BEGIN TRANSACTION executed, but neither COMMIT or ROLLBACK did.
The usual reason a stored procedure with a try-transaction block returns that message is the error: table (or other object) not found. This is NOT caught in the try-catch block.
Check the object names exist on runtime.
Also, make sure to run a ROLLBACK manually on the session that spawned this message, if you still haven't fixed the proc.
Documentation:
The following types of errors are not handled by a CATCH block when
they occur at the same level of execution as the TRY...CATCH
construct:
(........)
Object name resolution errors
As you can see, there are also other errors not caught which may be the problem. Check out the possibilities. Many of these error, including the object name resolution, can be caught if you put the procedure CALL inside a try-catch block.
begin try
exec [dbo].[prcInsDataFeesForVersion] ..............
end try
begin catch
SELECT @ErrMsg = ERROR_PROCEDURE() + ': ' + ERROR_MESSAGE() + ' (Error No. ' + Ltrim(Str(ERROR_NUMBER())) + ') ' + ' at line # ' + Ltrim(Str(ERROR_LINE()))
,@RetVal = 201
end catch
This might not sound handy, but at least you can use it to find the error out.
answered Mar 8 at 6:54
George MenoutisGeorge Menoutis
2,833521
The fact that lead to this error is that the BEGIN TRANSACTION executed, but neither COMMIT or ROLLBACK did.
The usual reason a stored procedure with a try-transaction block returns that message is the error: table (or other object) not found. This is NOT caught in the try-catch block.
Check the object names exist on runtime.
Also, make sure to run a ROLLBACK manually on the session that spawned this message, if you still haven't fixed the proc.
Documentation:
The following types of errors are not handled by a CATCH block when
they occur at the same level of execution as the TRY...CATCH
construct:
(........)
Object name resolution errors
As you can see, there are also other errors not caught which may be the problem. Check out the possibilities. Many of these error, including the object name resolution, can be caught if you put the procedure CALL inside a try-catch block.
begin try
exec [dbo].[prcInsDataFeesForVersion] ..............
end try
begin catch
SELECT @ErrMsg = ERROR_PROCEDURE() + ': ' + ERROR_MESSAGE() + ' (Error No. ' + Ltrim(Str(ERROR_NUMBER())) + ') ' + ' at line # ' + Ltrim(Str(ERROR_LINE()))
,@RetVal = 201
end catch
This might not sound handy, but at least you can use it to find the error out.
answered Mar 8 at 6:54
George MenoutisGeorge Menoutis
2,833521
edited Mar 8 at 7:05
answered Mar 8 at 6:54
George MenoutisGeorge Menoutis
2,833521
answered Mar 8 at 6:54
George MenoutisGeorge Menoutis
2,833521
answered Mar 8 at 6:54
George MenoutisGeorge Menoutis
2,833521
2,833521
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55057994%2fsql-server-transaction-count-after-execute-indicates-a-mismatching-number-of-beg%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Scattering NOLOCK hints in your code inside of a transaction is usually a sign of blind pattern usage. Highly doubtful that this hint is appropriate - especially when the queries are used to insert/update actual rows. The many uses of DISTINCT are also highly suspicious. Deleting from a table using the nolock hint is pointless, as is applying nolock hints to local temp tables. Truncating a table before dropping it makes no sense. Use of goto. <Big heavy sigh>. Get help!
– SMor
Mar 8 at 12:55