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

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

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.
@James: It might make sense if you formally accept it then, even if you actually figured out your solution yourself.
@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

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

4 Comments

Related