Post reply

How to avoid calling a user defined function multiple times on a select or update?

  • October 11, 2011 at 7:57 am

    #249660

    I am calling user defined function multiple times to calculate some values.

    Let's say Select dividend/GetPrice() as dividendYield, GetPrice()/Bookvalue as PriceToBookValue, GetPrice/Earnings as PERatio From SecurityDetails.

    Here I am calling the same GetPrice() user defined function three times to calculate three different values or ratios.

    Is there any way to just call once and use that in multiple calculations ?

    Thank you

    Sayap

  • October 11, 2011 at 8:05 am

    #1392873

    declare @someValue money

    set @someValue = getprice()

    Select dividend/@someValue as dividendYield, @someValue/Bookvalue as PriceToBookValue, @someValue/Earnings as PERatio From SecurityDetails.

    It is possible SQL could be smart enough to now the function return is consistent for a single row and not invoke it multiple times, but it may not actually do that. (it will do that with intrinsic functions)

    The probability of survival is inversely proportional to the angle of arrival.

  • October 11, 2011 at 8:05 am

    #1392874

    Select

    dividend/t.price as dividendYield,

    t.price/Bookvalue as PriceToBookValue,

    t.price/Earnings as PERatio

    From SecurityDetails, (select GetPrice() price) t

    I Have Nine Lives You Have One Only
    THINK!

  • October 11, 2011 at 8:32 am

    #1392895

    Thank you handkot and sturner for responding on this.

    Sorry I missed specifying the parameter for GetPrice() function.

    GetPrice() function actually has to have a SecurityId parameter to get the corresponding security price.

    Here is the update query:

    Select dividend/GetPrice(SecurityId) as dividendYield, GetPrice(SecurityId)/Bookvalue as PriceToBookValue, GetPrice(SecurityId)/Earnings as PERatio From SecurityDetails

    Could you please advise?

    Thank You

    Sayap

  • October 11, 2011 at 8:37 am

    #1392898

    Select dividend/t.price as dividendYield, t.price/Bookvalue as PriceToBookValue, t.price/Earnings as PERatio From SecurityDetails

    cross apply (select GetPrice(SecurityId) price) t

    I Have Nine Lives You Have One Only
    THINK!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply