April 9, 2010 at 7:45 am
HI ALL
Please assist me i'm executing a stored proc and experience the following error msg
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
below is a stored proc throwing the error msg on execution:
CREATER PROCEDURE [dbo].[spUTBasicUnitTrustDetailsWithSummary]
@ValueDate SMALLDATETIME
--WITH ENCRYPTION
AS
declare @shareacctypeid int
declare @sharestlacctypeid int
declare @uttradingacctypeid int
declare @calltypeid int
declare @fixtureacctype int
declare @tradingacctypeid int --patrick
set @shareacctypeid = dbo.fnAccountType('Shares')
set @sharestlacctypeid = dbo.fnAccountType('Shares Settlement')
set @uttradingacctypeid = dbo.fnAccountType('Unit Trust Trading')
set @calltypeid = dbo.fnAccountType('Call')
set @fixtureacctype = dbo.fnAccountType('Fixture')
set @tradingacctypeid = dbo.fnAccountType('Trading')
SELECTut.[ID],
ut.[Name],
ut.[CounterpartyID],
ut.[InitialOfferPrice],
ut.[CreationDate],
accs.[ID] AS [SharesAccountID],
accs.[AccountNo] AS [SharesAccountNo],
accst.[ID] AS [SharesStlAccountID],
accst.[AccountNo] AS [SharesStlAccountNo],
accc.[ID] AS [CallAccountID],
accc.[AccountNo] AS [CallAccountNo],
accf.[ID] AS [FixtureAccountID],
accf.[AccountNo] AS [FixtureAccountNo],
acct.[ID] AS [UtTradingAccountID],
acct.[AccountNo] AS [UtTradingAccountNo],
acctt.[ID] AS [TradingAccountID],
acctt.[AccountNo] AS [TradingAccountNo],
ISNULL(up.[OfferPrice], ut.[InitialOfferPrice]) AS [OfferPrice],
ISNULL(up.[BidPrice], 0) AS [BidPrice],
ISNULL(dbo.fnGetCharges(ut.[ID], @ValueDate), 0) AS [Charges],/*patrick charges*/
ISNULL([dbo].[fnGetGAVValue](ut.[ID], @ValueDate), 0) -
ISNULL(dbo.fnGetCharges(ut.[ID], @ValueDate), 0)/*patrick charges*/
AS [PortfolioValue],
'Offer Price: ' + CONVERT(VARCHAR(50), CAST(ISNULL(up.[OfferPrice], ut.[InitialOfferPrice]) AS DECIMAL(38, 6)), 1) + CHAR(10) +
'Bid Price: ' + CONVERT(VARCHAR(50), CAST(ISNULL(up.[BidPrice], 0) AS DECIMAL(38, 6)), 1) + CHAR(10) +
'NAV: ' + CONVERT(VARCHAR(50), CAST(
ISNULL([dbo].[fnGetGAVValue](ut.[ID], @ValueDate), 0) -
ISNULL(dbo.fnGetCharges(ut.[ID], @ValueDate), 0)/*patrick charges */
AS DECIMAL(38, 2)), 1)
AS Summary
FROM tblUTUnitTrust ut
--INNER JOIN tblCounterparty cp ON cp.[ID] = ut.[CounterpartyID]
INNER JOIN tblAccount accs ON accs.[CounterpartyID] = ut.[CounterpartyID]
AND accs.[AccountType] = @shareacctypeid
LEFT JOIN tblAccount accst ON accst.[CounterpartyID] = ut.[CounterpartyID]
AND accs.[AccountType] = @sharestlacctypeid
INNER JOIN tblAccount accc ON accc.[CounterpartyID] = ut.[CounterpartyID]
AND accc.[AccountType] = @calltypeid
INNER JOIN tblAccount accf ON accf.[CounterpartyID] = ut.[CounterpartyID]
AND accf.[AccountType] = @fixtureacctype
INNER JOIN tblAccount acct ON acct.[CounterpartyID] = ut.[CounterpartyID]
AND acct.[AccountType] = @uttradingacctypeid
INNER JOIN tblAccount acctt ON acctt.[CounterpartyID] = ut.[CounterpartyID]
AND acctt.[AccountType] = @tradingacctypeid
LEFT OUTER JOIN dbo.fnUTAllUnitPrice(@ValueDate) up ON up.[UnitTrustID] = ut.[ID]
the same is happening when executing the function below
CREATE FUNCTION [dbo].[fnUTUnitTrustCompute]
(@UnitTrustID BIGINT, @ValueDate SMALLDATETIME)
RETURNS @tbl TABLE (
UnitTrustID BIGINT,
UnitTrustFeeType INT,
UnitTrustFeeTypeName VARCHAR(50),
NAV DECIMAL(38, 5),
Perc DECIMAL(38, 5),
Fee DECIMAL(38, 5),
FeeToDate DECIMAL(38, 5),
FeeCumulative DECIMAL(38, 5)
)
AS
BEGIN
DECLARE @InterestRate DECIMAL(38, 5)
DECLARE @InterestAmount DECIMAL(38, 5)
DECLARE @PreviousInterestAmount DECIMAL(38, 5)
DECLARE @YearLength INT
SELECT @YearLength = dbo.fnNumericParameter('Year Length')
INSERT INTO @tbl (
UnitTrustID,
UnitTrustFeeType,
UnitTrustFeeTypeName,
NAV,
Perc,
Fee,
FeeToDate
)
SELECTut.[ID] AS [UnitTrustID],
utft.[Type],
utft.[Name] AS [UnitTrustFeeTypeName],
ISNULL(dbo.fnGetGAVValue(ut.[ID], @ValueDate), 0) -
ISNULL(dbo.fnGetCharges(ut.[ID], @ValueDate), 0),
CASE
WHEN ISNULL(utft.[Percentage] , 0) <> 0 THEN utft.[Percentage]
ELSE utft.[AnnualValue]
END,
CASE
WHEN ISNULL(utft.[Percentage] , 0) <> 0 THEN
((utft.[Percentage]/ 100) * ISNULL(dbo.fnGetGAVValue(ut.[ID], @ValueDate), 0)
/ @YearLength)
ELSE utft.[AnnualValue] / @YearLength
END,
CASE
WHEN [PeriodType] = dbo.fnPeriodType('Monthly') AND DATEPART(day , @ValueDate) = 1 THEN 0
WHEN [PeriodType] = dbo.fnPeriodType('Quarterly') AND
(DATEPART(quarter , @ValueDate) <> DATEPART(quarter , @ValueDate - 1)) THEN 0
WHEN [PeriodType] = dbo.fnPeriodType('Yearly') AND
(DATEPART(year , @ValueDate) <> DATEPART(year , @ValueDate - 1)) THEN 0
ELSE ISNULL(itd.[FeeCumulative], 0)
END
FROM tblUTUnitTrust ut
INNER JOIN tblUTUnitTrustFeeType utft ON utft.[Type] IS NOT NULL
LEFT JOIN (
SELECTTOP 100 PERCENT
[ID],
[UnitTrustID],
[UnitTrustFeeType],
[FeeCumulative]
FROM tblUTUnitTrustFees utf
WHERE [UnitTrustID] = @UnitTrustID
AND [Date] < @ValueDate
AND NOT EXISTS (
SELECT [ID]
FROM tblUTUnitTrustFees utf2
WHERE utf2.[UnitTrustID] = @UnitTrustID
AND utf2.[UnitTrustFeeType] = utf.[UnitTrustFeeType]
AND utf2.[Date] > utf.[Date]
AND utf2.[Date] < @ValueDate)
ORDER BY [Date] DESC
) AS itd ON itd.[UnitTrustID] = ut.[ID]
AND itd.[UnitTrustFeeType] = utft.[Type]
WHERE utft.[UnitTrustID] = @UnitTrustID AND ut.[ID] = @UnitTrustID
UPDATE @tbl
SET FeeCumulative = Fee + FeeToDate
RETURN
END
April 9, 2010 at 7:53 am
Do you have a batch terminator "GO" at the very END of the procedure and function code window?
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 9, 2010 at 8:29 am
i just added it(Batch terminator) and still giving the same error message
April 9, 2010 at 8:33 am
It doesn't APPEAR to be in the proc. One of your functions maybe?
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 9, 2010 at 8:46 am
both make a call to this function which shown below
and that's where i think the error is originating
CREATE FUNCTION [dbo].[fnUTUnitTrustNAV] (
@UnitTrustID INT,
@ValueDate SMALLDATETIME
)
RETURNS @tbl TABLE (
[ID] BIGINT,
[CallBalance] DECIMAL(38, 5),
[TradingBalance] DECIMAL(38, 5),
[UnitTrustTradingBalance] DECIMAL(38, 5),
[MoneyMarket] DECIMAL(38, 5),
[FixtureBalance] DECIMAL(38, 5),
[Equities] DECIMAL(38, 5),
[SharesBalance] DECIMAL(38, 5),
[OtherAssets] DECIMAL(38, 5),
[OutstandingDividends] DECIMAL(38, 5),
[AccruedInterest] DECIMAL(38, 5),
[GAV] DECIMAL(38, 5)
)
AS
BEGIN
DECLARE @UtCpID INT
DECLARE @EQPortfolioValue DECIMAL(38, 8)
,@MMPortfolioValue DECIMAL(38, 8)
,@OutstandingDividends DECIMAL(38, 8)
,@OtherAssetsValue DECIMAL(38, 8)
,@UnitTrustTradingBalance DECIMAL(38, 8)
,@TradingBalance DECIMAL(38, 8)
,@CallBalance DECIMAL(38, 8)
,@SharesBalance DECIMAL(38, 8)
,@GAV DECIMAL(38, 8)
SELECT @UtCpID= [CounterpartyID]
FROM tblUTUnitTrust
WHERE [ID] = @UnitTrustID
INSERT INTO @tbl (
[ID],
[CallBalance],
[TradingBalance],
[UnitTrustTradingBalance],
[FixtureBalance],
[SharesBalance],
[OtherAssets],
[OutstandingDividends]
)
SELECT@UnitTrustID,
SUM([CallBalance]),
SUM([TradingBalance]),
SUM([UnitTrustTradingBalance]),
SUM([FixtureBalance]),
SUM([SharesBalance]),
SUM([OtherAssets]),
SUM([OutStandingDividends])
FROM (
SELECTCASE
WHEN [AccountType] = dbo.fnAccountType('Call') THEN dbo.fnSiftAvailableBalance([ID], @ValueDate)
ELSE 0
END AS [CallBalance],
CASE
WHEN [AccountType] = dbo.fnAccountType('Trading') THEN dbo.fnSiftAvailableBalance([ID], @ValueDate)
ELSE 0
END AS [TradingBalance],
CASE
WHEN [AccountType] = dbo.fnAccountType('Unit Trust Trading') THEN dbo.fnSiftAvailableBalance([ID], @ValueDate)
ELSE 0
END AS [UnitTrustTradingBalance],
CASE
WHEN [AccountType] = dbo.fnAccountType('Fixture') THEN dbo.fnSiftAvailableBalance([ID], @ValueDate)
ELSE 0
END AS [FixtureBalance],
CASE
WHEN [AccountType] = dbo.fnAccountType('Shares') THEN dbo.fnSiftAvailableBalance([ID], @ValueDate)
ELSE 0
END AS [SharesBalance],
ISNULL(dbo.fnOtherAssetPortfolioValue(acc.[CounterpartyID], @ValueDate), 0) AS [OtherAssets],
ISNULL(dbo.fnAccountOutstandingDividends(acc.[ID], @ValueDate), 0) AS [OutstandingDividends]
FROM tblAccount acc
WHERE ([CounterpartyID] = @UtCpID OR [UnitTrustID] = @UnitTrustID)
AND [acc].[Used] = 1
) AS itd
UPDATE @tbl
SET [Equities] = dbo.fnEQSharesPortfolioValue(@UtCpID, @ValueDate)
DECLARE @AccruedInterest DECIMAL(38, 5);
DECLARE @AccountID INT;
SELECT @AccountID = acc.[ID]
FROM [tblAccount] acc
INNER JOIN tblAccountType act
ON act.[Type] = acc.[AccountType] AND act.[MMAllowAllocation] = 1
WHERE ([CounterpartyID] = @UtCpID OR [UnitTrustID] = @UnitTrustID)
AND [acc].[Used] = 1
IF @AccountID > 0 BEGIN
SELECT @AccruedInterest = SUM([InterestAccrued])
FROM [fnMMExtendedDealDetails]
(NULL, --@DealID INT
@AccountID, --@AccountID INT,
NULL, --@StartValueDate DATETIME
NULL, --@EndValueDate DATETIME
@ValueDate, --@StartMaturityDate DATETIME
NULL, --@EndMaturityDate DATETIME
@ValueDate, --DATETIME
1, --@Confirmed BIT
0, --@Rejected BIT
NULL, --@Matured BIT
NULL, --@Settled BIT
0 --@Terminated BIT
)
END;
UPDATE @tbl SET [AccruedInterest] = ISNULL(@AccruedInterest, 0);
--calculating GAV
SELECT@EQPortfolioValue = SUM([Equities]),
@CallBalance = SUM([CallBalance]),
@TradingBalance = SUM([TradingBalance]),
@UnitTrustTradingBalance = SUM([UnitTrustTradingBalance]),
@MMPortfolioValue = SUM([FixtureBalance]),
@SharesBalance = SUM([SharesBalance]),
@OtherAssetsValue = SUM([OtherAssets]),
@OutstandingDividends = SUM([OutStandingDividends]),
@AccruedInterest = SUM([AccruedInterest])
FROM @tbl
SELECT@GAV =
@EQPortfolioValue +
@MMPortfolioValue +
@OtherAssetsValue +
@OutstandingDividends +
@SharesBalance +
@UnitTrustTradingBalance +
@CallBalance +
@TradingBalance +
@AccruedInterest
UPDATE @tbl SET [GAV] = ISNULL(@GAV, 0);
RETURN
END
GO
This function calculates the GAV Value that is used by the stored proc and function throwing that err message
to get GAV value the above function i use another function which selects GAV returned by the above function [dbo].[fnUTUnitTrustNAV]
The function passing GAV to the proc and function throwing an err message is given by:
CREATE FUNCTION [dbo].[fnGetGAVValue]
(
@UnitTrustID INT,
@ValueDate SMALLDATETIME
)
RETURNS DECIMAL(38, 5)
AS
BEGIN
DECLARE @GAV DECIMAL(38, 5);
SELECT @GAV = ISNULL([GAV], 0)
FROM [dbo].[fnUTUnitTrustNAV] (@UnitTrustID, @ValueDate)
RETURN ISNULL(@GAV ,0)
END
GO
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply