Problem using a UDF as computed col: Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Question

Hello.

My question is a little subjective and might be a little unclear since I don't know where in the UDF crashes, or whether the error is caused in the function itself or from other resources using a column in this table.

Let me just tell you the story.

I had a function (that used as a computed-column for QuoteItemsGroupFeature.SalesPrice), that the following was its content, and it worked great (The function returns @Total money, and the parameter supplied is @QuoteItemsGroupFeatureId):

SELECT @Total =         
    CASE QuoteItemsGroupFeature.Feature
        WHEN 1 THEN - ((QuoteItemsGroup.BaseSalesPrice + QuoteItemsGroup.AccumulatedSalesPrice + ISNULL(SUM(ParentQuoteItemsGroupFeature.SalesPrice), 0)) * QuoteItemsGroupFeature.Amount)
        WHEN 2 THEN ISNULL(SUM(ParentQuoteItemsGroupFeature.SalesPrice), 0) - QuoteItemsGroupFeature.Amount
        WHEN 3 THEN (QuoteItemsGroup.BaseSalesPrice + QuoteItemsGroup.AccumulatedSalesPrice + ISNULL(SUM(ParentQuoteItemsGroupFeature.SalesPrice), 0)) * QuoteItemsGroupFeature.Amount
        WHEN 4 THEN ISNULL(SUM(ParentQuoteItemsGroupFeature.SalesPrice), 0) + QuoteItemsGroupFeature.Amount
    END
FROM QuoteItemsGroupFeature
    INNER JOIN QuoteItemsGroup ON QuoteItemsGroupFeature.QuoteItemsGroupId = QuoteItemsGroup.QuoteItemsGroupId
    LEFT OUTER JOIN QuoteItemsGroupFeature AS ParentQuoteItemsGroupFeature ON QuoteItemsGroupFeature.ParentQuoteItemsGroupFeatureId = ParentQuoteItemsGroupFeature.QuoteItemsGroupFeatureId
WHERE QuoteItemsGroupFeature.QuoteItemsGroupFeatureId = @QuoteItemsGroupFeatureId
GROUP BY QuoteItemsGroup.BaseSalesPrice, QuoteItemsGroup.AccumulatedSalesPrice, QuoteItemsGroupFeature.Feature, QuoteItemsGroupFeature.Amount

The customer asked me to change the calculation behavior for QuoteItemsGroupFeature.Feature 2 and 4, to the following:

SELECT @Total =     
    CASE 
        WHEN QuoteItemsGroupFeature.Feature = 1 THEN - ((QuoteItemsGroup.BaseSalesPrice + QuoteItemsGroup.AccumulatedSalesPrice + ISNULL(SUM(ParentQuoteItemsGroupFeature.SalesPrice), 0)) * QuoteItemsGroupFeature.Amount)
        WHEN QuoteItemsGroupFeature.Feature = 3 THEN (QuoteItemsGroup.BaseSalesPrice + QuoteItemsGroup.AccumulatedSalesPrice + ISNULL(SUM(ParentQuoteItemsGroupFeature.SalesPrice), 0)) * QuoteItemsGroupFeature.Amount
        WHEN QuoteItemsGroupFeature.Feature IN (2, 4) THEN QuoteItemsGroupFeature.Cost + (QuoteItemsGroupFeature.Cost * (SELECT CASE COUNT(DISTINCT Room.Markup) WHEN 1 THEN SUM(DISTINCT Room.Markup) Else QuoteItemsGroup.SpecialMarkup END))
    END           
FROM QuoteItemsGroupFeature
    INNER JOIN QuoteItemsGroup ON QuoteItemsGroupFeature.QuoteItemsGroupId = QuoteItemsGroup.QuoteItemsGroupId
    INNER JOIN QuoteItemsGroupQuoteItem ON QuoteItemsGroupQuoteItem.QuoteItemsGroupId = QuoteItemsGroup.QuoteItemsGroupId
    INNER JOIN QuoteItem ON QuoteItem.QuoteItemId = QuoteItemsGroupQuoteItem.QuoteItemId
    INNER JOIN Room ON QuoteItem.RoomId = Room.RoomId
    LEFT OUTER JOIN QuoteItemsGroupFeature AS ParentQuoteItemsGroupFeature ON QuoteItemsGroupFeature.ParentQuoteItemsGroupFeatureId = ParentQuoteItemsGroupFeature.QuoteItemsGroupFeatureId
WHERE QuoteItemsGroupFeature.QuoteItemsGroupFeatureId = @QuoteItemsGroupFeatureId
GROUP BY QuoteItemsGroupFeature.Cost, QuoteItemsGroup.BaseSalesPrice, QuoteItemsGroup.AccumulatedSalesPrice, QuoteItemsGroup.SpecialMarkup, QuoteItemsGroupFeature.Feature, QuoteItemsGroupFeature.Amount

The function (the second) for itself works good and does the work as long as I don't set it as the computed-column; once I do so - I get the following error:

Msg 217, Level 16, State 1, Line 14

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

I believe this might be not enough information, I guess I will add specific info by request.

NOTE: I tried to create a view that returns this function according to the ID field, and it works just great! Thanks a lot.