Calling a UDF during table creation

Question

I have a UDF that calculates a score based on a range of values that are going in to the table.

The UDF has to be applied/called during the table creation process, this is where I'm having a little trouble.

The UDF had to be created using case when methods only, so I can't change much on that but I'm sure I've just got something a little off.

I'm not sure if the answer is already out there or not, but I haven;t stumbled across it yet so apologies if this is something already answered.

Here is the UDF which is created first

--Create UDF
create function [dbo].[cupidscoreUDF]
(
    @gender char(1),
    @name varchar(15),
    @dob datetime,
    @weight int,
    @height int,
    @smoker bit,
    @salary int
)
returns int
as
begin
    declare @score int

    -- To determine age in years
    declare @Age int 
    select @Age = DATEDIFF(YEAR, @dob, GETDATE())

    select 
        @score = case
                    when @Age between 20 and 30 then 5
                    when @Age between 31 and 40 then 4
                    when @Age between 41 and 50 then 3
                    when @Age > 50 then 2
                    else 0
                end

    -- To determine the height/weight ratio
    declare @WeightHeight int

    set @WeightHeight = @weight / @height 

    set
        @score = @score +
                    case
                        when @WeightHeight between 20 and 25 then 1
                        when @WeightHeight between 25 and 30 then 3
                        when @WeightHeight between 30 and 35 then 4
                        when @WeightHeight between 35 and 40 then 2
                        else 0
                    end

    -- If non-smoker add 2 points
    if @smoker = 0
        set @Score = @Score + 2

    -- To determine score by salary
    set
        @score = @score + 
                    case
                        when @salary < 50000 then 1
                        when @salary between 500001 and 60000 then 2
                        when @salary between 60001 and 70000 then 3
                        when @salary > 70000 then 4
                    end

    return @score
end
;

Now here's what I've got for the table creation process

-- Create Member_Profile table
create table Member_Profile
(
MemberID int primary key,
Gender varchar(6),
Name varchar(50),
Dob datetime,
Weight int,
Height int,
Smoker bit,
Salary int,
Cupid as dbo.cupidscoreUDF
)
GO

insert into Member_Profile (Gender, Name, Dob, Weight, Height, Smoker, Salary) values ('Male','James',19931115, 75, 180, 0, 80000);
insert into Member_Profile (Gender, Name, Dob, Weight, Height, Smoker, Salary) values ('Female','Rosie',19870912, 45, 150, 0, 100000);
insert into Member_Profile (Gender, Name, Dob, Weight, Height, Smoker, Salary) values ('Male','Richard',19630402, 95, 168, 1, 200000);

select * from Member_Profile

The UDF takes the member's info and then calculates their 'cupid' score from that, which is then inserted along with everything else in to the table.

Any help using the UDF would be great

You can try TRIGGER while inserting data to the table...

Jesuraja
– Jesuraja

2014-09-01 04:48:33 +00:00
Commented Sep 1, 2014 at 4:48 — Jesuraja
– Jesuraja, Commented Sep 1, 2014 at 4:48
Your UDF expects 7 parameters. I need to pass them

cha
– cha

2014-09-01 04:48:45 +00:00
Commented Sep 1, 2014 at 4:48 — cha
– cha, Commented Sep 1, 2014 at 4:48

Himanshu · Accepted Answer · 2014-09-01 06:04:40Z

1

Try to use this for Cupid column:

Cupid as dbo.cupidscoreUDF(Gender, Name, Dob, Weight, Height, Smoker, Salary)

edited Sep 1, 2014 at 6:04

Himanshu

4,41116 gold badges34 silver badges41 bronze badges

answered Sep 1, 2014 at 5:38

Sabin B

29312 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tristan · Accepted Answer · 2014-09-01 10:23:56Z

I would very seriously suggest moving the calling of the UDF to the insert/update instead of the table definition. To make this easier you could make a simple procedure to insert/update that calls the UDF. That way you avoid having to give the data twice.

here is a very generic example, in this example it looks a bit weird to do all the extra steps for something so simple but that quickly changes when you apply actual scenarios.

use demo
go

create table dbo.example_table (
id int identity(1,1) primary key,
some_value_1 int,
some_value_2 int,
some_calculation int)

go

create function dbo.calculator_function (
@value1 int,
@value2 int)
returns int
as
begin
declare @result int = (select @value1 + @value2 as result)
return @result
end

go

create procedure dbo.insert_example 
    @value1 int,
    @value2 int
as
insert dbo.example_table (some_value_1,some_value_2,some_calculation) 
select @value1, @value2, dbo.calculator_function(@value1,@value2)

go

exec dbo.insert_example 1,2

go 

select * from example_table

Muz · Accepted Answer · 2014-09-02 12:01:14Z

0

I'm assuming you're at VUW, and I'm assuming you're doing 341 like myself. Use a trigger to call your UDF -I've given you enough to get started on it, you will need to declare and set whatever variables you deem necessary for it to work

CREATE TRIGGER [dbo].[CalculateCupid]
 ON [dbo].[Member_Profile]
 AFTER INSERT
AS
BEGIN




UPDATE DBO.Member_Profile 
SET Cupid_Score = @Cupid_Score WHERE MemberID=@MemberID

END

answered Sep 2, 2014 at 12:01

Muz

396 bronze badges

4 Comments

James Over a year ago

I've done it a little differently, as you'll see above, using the UDF as a table column and calculating the score at the same time as insert - I've got it working now.

Andriy M Over a year ago

@James: You mean you did it as in sabin bio's answer?

Andriy M Over a year ago

@James: It might make sense if you formally accept it then, even if you actually figured out your solution yourself.

James Over a year ago

@AndriyM sorry, didn't know how to do this. Have sorted it now

Collectives™ on Stack Overflow

Calling a UDF during table creation

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related