ALTER FUNCTION [dbo].[SLN] 
(
	@GLH  as smallint, @Year AS smallint, @A27 AS smalldatetime, @A28 AS smalldatetime
)
RETURNS Float
AS --calculates if aim is active on first census point
 BEGIN
  DECLARE @AGLH AS SmallInt, @SLNResult AS Float

  -- Get ALGH
  SELECT @AGLH = (SELECT dbo.AGLH (@GLH, @Year, @A27, @A28))

  SET @SLNResult = (@AGLH / 450)

  IF @SLNResult > 1.75 
  BEGIN
    SET @SLNResult = 1.75
  END

  RETURN @SLNResult
 END

quote:

---

Originally posted by gavinjb

Hi,

I have writen a simple Function that calls various other functions and should return a value of 1.75 or less, only problem is that when the value is < 1 it return 0 for example one set of parameters I passed if I did manually I would get a result of 0.54 but the function returns 0 can anyone please help.

ALTER FUNCTION [dbo].[SLN] 
(
	@GLH  as smallint, @Year AS smallint, @A27 AS smalldatetime, @A28 AS smalldatetime
)
RETURNS Float
AS --calculates if aim is active on first census point
 BEGIN
  DECLARE @AGLH AS SmallInt, @SLNResult AS Float

  -- Get ALGH
  SELECT @AGLH = (SELECT dbo.AGLH (@GLH, @Year, @A27, @A28))

  SET @SLNResult = (@AGLH / 450.0)

  IF @SLNResult > 1.75 
  BEGIN
    SET @SLNResult = 1.75
  END

  RETURN @SLNResult
 END

If I called this function with

select dbo.SLN (486,2008,'04-Sep-2007', '03-Jul-2009')

I would expect to get 0.54 but I am getting 0. for info the dbo.AGLH function returns a value of 243.

Thanks,



Gavin,

---