Is it bad to use a Sql Server Scalar UDF in this scenario?

Question

Hi Folks,

I'm trying to generate some sql that is used to calculate some final scores -> think kids at school and their end of year scores.

I was going to have about 5 or so Scalar UDF's that, accept some simple values (eg. current score, subjectid, whatever) and then spit out a decimal value.

eg.

CREATE FUNCTION [dbo].[GetRatingModifier]
(
    @ExamScore DECIMAL(6, 2),
    @Project1Score DECIMAL(6, 2),
    @Project1Score DECIMAL(6, 2),
    @Project1Score DECIMAL(6, 2),
    @SubjectTypeId TINYINT
)
RETURNS DECIMAL(8,5)
AS
BEGIN
    DECLARE @Score DECIMAL(8,5)

    SELECT @Score = (CASE @Project1Score
                     WHEN 1 THEN 10
                     WHEN 2 THEN 12.4
                      ....) +
                    (CASE blah.. u get the drift)..
    RETURN @Score
END

The logic has only maths. No select xxx from table yyy etc..

So, is this ok to do with regards to performance?

Answer 1

Sure, no problem - performance will be almost the same as if when you would do it inline at every SELECT. I don't think this should be any problem at all - quite the contrary, putting this into an UDF seems like a really good idea! (to keep your T-SQL code clean and simple).

Marc

Answer 2

Yes, it's a bad idea because you should put all your score weights in a table and perform a join to get the result.