Very Simple User Define Function Returning Zero

• Wayne-950947

  Ten Centuries

  Points: 1122

  More actions

  January 26, 2009 at 7:59 pm

  #128265

  Hi folks

  Here's the UDF:

  CREATE FUNCTION [dbo].[GET_EMA_Multiplier](@Period INT)

  RETURNS FLOAT AS

  BEGIN

  RETURN 2/(@Period + 1))

  END

  -- On executing this function with the following statement

  -- It returns 0 (zero) and I'm expecting 0.15385...

  SELECT dbo.GET_EMA_Multiplier(12);

  I've tried many different variations of this function including data types, but they still return Zero.

  Thanks in advance,

  Wayne

• Lowell

  SSC Guru

  Points: 323507

  More actions

  January 26, 2009 at 9:02 pm

  #932972

  SQL server doesn't bother with changing data tytpes when it sees integer divided by integer...it returns an integer value.

  change so that your 2 is 2.0:

  RETURN 2.0/(@Period + 1.0))

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• SQLback

  SSCrazy

  Points: 2897

  More actions

  January 26, 2009 at 9:05 pm

  #932976

  When MS SQL Server performs an operation on INT values, it returns an INT value. This means the division of two INT values will always return an INT.

  The solution is to cast at least one of the two values to a FLOAT

  Try this..

  CREATE FUNCTION [dbo].[GET_EMA_Multiplier](@Period INT)

  RETURNS FLOAT AS

  BEGIN

  RETURN 2/CAST((@Period + 1) AS FLOAT)

  END

• arun.sas

  SSChampion

  Points: 11831

  More actions

  January 26, 2009 at 9:10 pm

  #932977

  Hi,

  your passing the int (round no) value as @Period,already in 2 also then int type

  try this

  CREATE FUNCTION TEST(@Period FLOAT)

  RETURNS FLOAT AS

  BEGIN

  RETURN 2/(@Period + 1)

  END

  SELECT dbo.TEST(12)

  you get the requirement

• Wayne-950947

  Ten Centuries

  Points: 1122

  More actions

  January 27, 2009 at 5:04 am

  #933103

  Many thanks folks - that was enlightening.

  Wayne