SQL Server Performance Forum – Threads Archive

using fn to evaluate ‘when – then’ statement

Hi
I am looking through a database schema where I have noticed that a particular
function is code with a lot of select @ref = CASE @code
WHEN agent THEN 46
WHEN client THEN 47 this goes on for about 300 WHEN statements. My question is would this be better in a table? Is this an eficient use of the function?
My instinct is to make this into a table, but I am unsure how efficient sql server is with this type of value evaluation statement. thanks
john

---

From your code snippet, this looks like some really bad use of UDF’s. On larger tables it is likely to be a performance killer. I guess I would also rather use a parameter table. —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

---

Also review from where this UDF is being called. Looks like a poor design choice – like having one entity impersonate 300 different entities.

---

thanks for the replies. the udf is being called – typically from other db procedures to evaluate the required number value of the parameter being supplied/called like select
@agent = dbo.fn_GetRef(Ágent’) I realise that this is poor design – but why is it poor use of udfs. For small evals like less that 10 I’d have no problems but there is 300 of these. thanks

---

Where does the ‘Agent’ string come from? Why can’t the source supply the numeric value instead? Looks like someone has replaced the "Select Case" syntax from VB (or similar) with the same in T-SQL. Evaluating 300 options will be handled much quicker if the options are recorded on an indexed column in a permanent table. It also allows you to use more abstract code, where the exact string ("Client" or "Agent") doesn’t need to be mentioned, so you can expand the list of options without having to rewrite any code.

---

If there are so many CASE WHENS then, why dont you store them in lookup table and join it main table? Madhivanan Failing to plan is Planning to fail

---

thanks All. this is just the point – normally I would put this into a table. I need a reason why it shouldn’t be designed like this using UDFs.
I don’t have any experience of UDFs so need a solid reason. The AGENT paramenter or any parameter being evalustaed in the function can come in as a parameter input from the user or another procedure which requires the evaluated numeric in order to look up some other value in another table or such thanks

---

The two major points have already been discussed: Maintainability
Adding a new option to the UDF involves changing code, with the risk of coding errors. With a lookup table, it’s just data entry on a lookup table – if there’s an error there then it will be easier to identify. Performance
The lookup actions against the table may well be cached, whereas UDF evaluations must be repeated each and every time they are called.

---

thanks Adriaan point requiring clarification. will the UDF be evaluated all the way to the end even if the value has been identified say midway through the When …Then statement? or will it stop when a value that satisfys the when statement is found? once again thanks

---

It WILL stop evaluating the WHEN , when the first match found. To see this in action execute the following code snippet.
SELECT CASE WHEN 1=1 THEN ‘OK’
WHEN 1/0 = 0 THEN ‘Not OK’
ELSE ‘This is unbelievable’
END
Note that if SQL Server has to evaluate the second expression, then the "Division by Zero" error will be thrown. Anyways scalar udfs are performance killers. The reason is that the udf is a cursor behind the scenes. SQL Server MVPs Kalen Delaney and Adam Machanic recently blogged about it here and here. Roji. P. Thomas
http://toponewithties.blogspot.com

---

thanks Roji

---

]]>