SQL Server Performance Forum – Threads Archive

Function Recall

I am calling the same function twice/more with different parameters in a single query..The query returns a wrong result..But if i run the same query with only one function, it returns a correct value. What can be the reason behind this? Can someone help me out with this…

---

Could you post some code? Bambola.

---

Below code snippet..If i change 0 to any other number(1 or 0.0 or null….) then it works fine…or if i change the 0 in the isNull function to 0.0 then also it works..my function fn_1 returns a numeric(21,6). In the below three isNull function if i comment atleast one of them it executes properly.. SUM(
CASE SUBSTRING(CONVERT(VARCHAR(10), rpt.mon_yr), 5, 2) WHEN substring(convert(varchar,rpt.mon_yr),5,6) THEN
ISNULL(user.fn_1(rpt.reg_id, rpt.v_id, rpt.p_id, rpt.mon_yr, 1,’ACCT1′),
ISNULL(user.fn_1(rpt.reg_id, rpt.v_id, rpt.p_id, rpt.mon_yr, 1,’ACCT2′),
ISNULL(user.fn_1(rpt.reg_id, rpt.v_id, rpt.p_id, rpt.mon_yr, 1,’ACCT3′), 0)))
ELSE 0 END)) ), (12.0)) – 1) * 100
I had modified the above code by giving different function names(all the functions have the same code) then it is working fine.. SUM(
CASE SUBSTRING(CONVERT(VARCHAR(10), rpt.mon_yr), 5, 2) WHEN substring(convert(varchar,rpt.mon_yr),5,6) THEN
ISNULL(user.fn_1(rpt.reg_id, rpt.v_id, rpt.p_id, rpt.mon_yr, 1,’ACCT1′),
ISNULL(user.fn_2(rpt.reg_id, rpt.v_id, rpt.p_id, rpt.mon_yr, 1,’ACCT2′),
ISNULL(user.fn_3(rpt.reg_id, rpt.v_id, rpt.p_id, rpt.mon_yr, 1,’ACCT3′), 0)))
ELSE 0 END)) ), (12.0)) – 1) * 100 i know it is not a good way to program..

---

padraj
could you post code of function please ?

---

…and please indicate the context within which you are calling this function. ISNULL() has been known to cause hiccups when used within UDFs. Nathan H.O.
Moderator
SQL-Server-Performance.com

---

]]>