asked on 7/4/2015

User Defined Function

I need to create a function which will return an average of Vacation hours based on Gender.I have written the following code:
CREATE FUNCTION fx_avgVacationHours
(@Gender varchar(50))
Returns int
AS
BEGIN
RETURN
(
SELECT AVG (VacationHours)
FROM HumanResources.Employee
where Gender=@Gender
)
end

Select dbo.fx_avgVacationHours(2)

But this code is returning me a NULL value.
Please let me know what wrong i am doing here.

Microsoft SQL Server 2008

Last Comment

Soumi Chakraborty

7/4/2015

Mike Eghtebas

7/4/2015

try (if Gnder column data is 1, 2, etc.):

CREATE FUNCTION fx_avgVacationHours
(@Gender int)
Returns int
AS
BEGIN
RETURN
SELECT  AVG (VacationHours)
  FROM HumanResources.Employee
where Gender=@Gender
end

Open in new window

Soumi Chakraborty

7/4/2015

ASKER

Thanks eghtebas. The function got created but can you please explain the logic behind it? Gender is not an integer value. Moreover the select statement is giving me an error. Please help me understand the logic.

ASKER CERTIFIED SOLUTION

Mike Eghtebas

7/4/2015

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Soumi Chakraborty

7/4/2015

ASKER

Thank you so much eghtebas. This time it worked and now I understand the logic as well.