Problem with 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

Transact-SQL (2000)

Problem with function

Author

Topic

raxbat
Yak Posting Veteran

52 Posts

Posted - 2008-03-04 : 08:57:46

Hello all!
Can You answer me why my function returns 0?
For example: @nom=0.0005 and @spread=10000

CREATE FUNCTION M(@nom decimal, @spread decimal)
returns decimal
as
begin
declare @a decimal
select @a=@nom

if (@nom>2/@spread) select @a=2/@spread
if (@nom<-2/@spread) select @a=-2/@spread
return @nom
end

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-04 : 09:14:10

decimal, with how many decimal places?
always put (x, y) after decimal.

decimal(12, 6)

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-04 : 09:17:36

x indicates precision & y the scale of your decimal value

raxbat
Yak Posting Veteran

52 Posts

Posted - 2008-03-04 : 09:48:32

the same problem. Is it correct:
CREATE FUNCTION M(@nom decimal(12,6), @spread int)
returns decimal(12,6)
as
begin
declare @a decimal(12,6)
select @a=@nom

if (@nom>2/@spread) select @a=2/@spread
if (@nom<-2/@spread) select @a=-2/@spread
return @a
end

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-03-04 : 09:52:11

is it not because @spread is an integer? make it a decimal as well

Em

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-04 : 09:53:40

quote:
Originally posted by raxbat

the same problem. Is it correct:
CREATE FUNCTION M(@nom decimal(12,6), @spread int)
returns decimal(12,6)
as
begin
declare @a decimal(12,6)
select @a=@nom

if (@nom>2.0/@spread) select @a=2.0/@spread
if (@nom<-2.0/@spread) select @a=-2.0/@spread
return @a
end

Change like this if you want to decimal result

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-03-04 : 09:55:21

i.e.....

quote:

CREATE FUNCTION M(@nom decimal(12,6), @spread int)
returns decimal(12,6)
as
begin
declare @a decimal(12,6)
select @a=@nom

if (@nom>2/@spread) select @a=2/@spread
if (@nom<-2/@spread) select @a=-2/@spread
return @a
end

when you're assigning the value to @a you're dividing an integer value by another integer value, thereby implicitly returning an integer, so if the value is less than 1 you will only get 0

Em

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources