user defined function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

user defined function

Author

Topic

adamlee
Starting Member

9 Posts

Posted - 2006-05-03 : 21:20:36

Hi,

Is it possible to convert below delphi function to user defined function in
SQL server?

function taxn(ran:double):double;
var
saSum: Double;
begin
saSum := ran - 1600;
if saSum <=0 then result := 0
else if saSum <= 475 then Result := (saSum-0)/(1 - 0.05)*0.05-0
else if saSum < 1825 then Result := (saSum-25)/(1 - 0.1)*0.1-25
else if saSum < 4375 then Result := (saSum-125)/(1 - 0.15) *0.15-125
else if saSum <= 16375 then Result := (saSum-375)/(1 - 0.2)*0.2-375
else if saSum <= 31375 then Result := (saSum-1375)/(1 - 0.25)*0.25-1375
else if saSum <= 45375 then Result := (saSum-3375)/(1 - 0.3)*0.3-3375
else if saSum <= 58375 then Result := (saSum-6375)/(1 - 0.35)*0.35-6375
else if saSum <= 70375 then Result := (saSum-10375)/(1 - 0.4)*0.4-10375
else if saSum > 70375 then Result := (saSum-15375)/(1 - 0.45)*0.45-15375;
end;

and call it from a trigger? such as :

update salary set tax=taxn(totalnet) //totalnet is another field value

Appreciate your help.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-03 : 21:28:52

try this

create function dbo.f_taxn
(
	@ran real
)
returns real
as
begin
	declare @saSum  real,
		@Result real

	select @saSum = @ran - 1600

	if @saSum <=0 select @Result = 0
	else if @saSum <= 475  select @Result = (@saSum-0)/(1 - 0.05)*0.05-0
	else if @saSum < 1825  select @Result = (@saSum-25)/(1 - 0.1)*0.1-25
	else if @saSum < 4375  select @Result = (@saSum-125)/(1 - 0.15) *0.15-125
	else if @saSum <= 16375  select @Result = (@saSum-375)/(1 - 0.2)*0.2-375
	else if @saSum <= 31375  select @Result = (@saSum-1375)/(1 - 0.25)*0.25-1375
	else if @saSum <= 45375  select @Result = (@saSum-3375)/(1 - 0.3)*0.3-3375
	else if @saSum <= 58375  select @Result = (@saSum-6375)/(1 - 0.35)*0.35-6375
	else if @saSum <= 70375  select @Result = (@saSum-10375)/(1 - 0.4)*0.4-10375
	else if @saSum > 70375  select @Result = (@saSum-15375)/(1 - 0.45)*0.45-15375;

	return @Result
end

to use it select dbo.f_tanx(yourvalue)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-05-03 : 22:25:10

As long as you've got a working function that operates off of one value in your table, an alternative to a trigger you can use the function as the expression in a computed column:

create table myTable (totalnet real, taxn as dbo.f_taxn(totalnet))

Be One with the Optimizer
TG

adamlee
Starting Member

9 Posts

Posted - 2006-05-04 : 00:07:36

Thank you, can I use it in a stored procedure like below?:

CREATE PROCEDURE caltotalnet AS
begin transaction
update salary set totalnet=housing+meal+overtime+car+phone+netwage
update salary set tax=dbo.f_taxn(totalnet)
commit
GO

....

Appreciate your help.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-04 : 00:13:22

yes. You can combined the 2 update statement into one if you wish.


update salary set totalnet=housing+meal+overtime+car+phone+netwage,
update salary set tax=dbo.f_taxn(totalnet)

adamlee
Starting Member

9 Posts

Posted - 2006-05-04 : 00:23:39

Thank you so much! You solved me a very big question!
I previously want to solve this calculation with calculated fields, but failed in saving the changes to the table, so I have to use trigger or stored procedure to calculate although all changes in DBGrid are not updated in the table immmediately.

THanks again.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-05-04 : 09:18:17

>> but failed in saving the changes to the table
In case you didn't know, if you have a computed column defined in a table, you don't insert or update that column. The defined expression maintains the values for you.

EDIT:
Sample code:


set nocount on
if object_id('dbo.f_test') > 0
	drop function dbo.f_test
go
create function dbo.f_test(@i int)
returns int
as
begin
	return @i + 1
end
go

create table TestComputedColumn (i int, computedVal as dbo.f_test(i))
go

insert TestComputedColumn (i) values (3)
select * from TestComputedColumn 

go

drop table TestComputedColumn
drop function dbo.f_test


output:
i           computedVal     
----------- ----------- 
3           4

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources