Performance improvement to a big if clause in SQL Server function

Question

I am maintaining a function in SQL Server 2005, that based on an integer input parameter needs to call different functions e.g.

IF @rule_id = 1
   -- execute function 1
ELSE IF @rule_id = 2
   -- execute function 2
ELSE IF @rule_id = 3
   ... etc

The problem is that there are a fair few rules (about 100), and although the above is fairly readable, its performance isn't great. At the moment it's implemented as a series of IF's that do a binary-chop, which is much faster, but becomes fairly unpleasant to read and maintain. Any alternative ideas for something that performs well and is fairly maintainable?

Answer 1

Can you change it so that it execs a function as a string? I'd normally recommend against this sort of dynamic sql, and there may be better ways if you step back and look at overall design... but with what is known here you may have found one of the rare exceptions when it's better.

ex:

set @functionCall = 'functionRootName' + @rule_id
exec @functionCall

Answer 2

I would suggest you generate the code programatically, eg. via XML+XSLT. the resulted T-SQL will be the same as you have now, but maintaining it would be much easier (adding/removing functions).

Inside a function you don't have much choice, using IFs is pretty much the only solution. You can't do dynamic SQL in functions (you can't invoke exec). If its a stored procedure, then you have much more libery as you can use dynamic SQL and have tricks like a lookup table: select @function = function from table where rule_id = @rule_id; exec sp_executesql @function;.

Answer 3

Whatever is calling the SQL function - why does it not choose the function?

This seems like a poorly chosen distribution of responsibility.