CODEWITHSUNDEEP
sqlsql-serveruser-defined-functions
Pure.Krome
Pure.Krome

Reputation: 86997

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

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?

Upvotes: 0

Views: 228

Answers (2)

erikkallen
erikkallen

Reputation: 34411

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.

Upvotes: 1

marc_s
marc_s

Reputation: 755157

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

Upvotes: 3

Related Questions