Help with UDF 1

[jmbcreative]

I am working on createinga UDF and I am stumped. At this point my basic question is this; in a UDF, if I want to use a field that I created within it as part of an equation, do I have to declare the field as a variable?

For example say if I have something like the following;

CREATE FUNCTION Example()
RETURNS int
AS
BEGIN
Declare @ReturnValue
SELECT AVG(ValueA)AS "AvgA", AVG(ValueB) AS "AvgB"
FROM dbo.Example
RETURN @ReturnValue = AvgA - AvgB
END

Is that a legitimate UDF, or do I declare values for the "AvgA", and "AvgB"?

 

[gmmastros]

if I want to use a field that I created within it as part of an equation, do I have to declare the field as a variable?

Technically, no.

However... you have multiple statements here. If you want to use values from one statement to another, you need to create variables.

In your example, there are several ways you could do this.

CREATE FUNCTION Example()
RETURNS int
AS
BEGIN
   Declare @ReturnValue
   Declare @AverageA Int
   Declare @AverageB Int

   SELECT @AverageA = AVG(ValueA), 
          @AverageB = AVG(ValueB)
   FROM   dbo.Example

   RETURN @ReturnValue = @AverageA - @AveragegB
END

Since there are multiple statements, you need to store the values in variables.

In this example, you could reduce this so you don't need to declare variables.

CREATE FUNCTION Example()
RETURNS int
AS
BEGIN
   Return( SELECT AVG(ValueA) - AVG(ValueB)
           FROM   dbo.Example)
END

or even....

CREATE FUNCTION Example()
RETURNS int
AS
BEGIN
   Return( SELECT AVG(ValueA - ValueB)
           FROM   dbo.Example)
END

Also.... beware of integer math. If ValueA is int, then Avg will be int also (eventhough you may not expect it).

To return fractional values from your AVG function, you need to convert the parameter to a numeric data type.

Ex:

Select Avg(IntColumn * 1.0)

Or

Select Avg(Convert(Decimal(10,2), IntColumn))




-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jmbcreative]

Thank you, George!