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.
Soumi ChakrabortyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
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

0
Soumi ChakrabortyAuthor Commented:
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.
0
Mike EghtebasDatabase and Application DeveloperCommented:
Then run it like:

Select dbo.fx_avgVacationHours('Male')

replace 'Male' with 'Female' whatever you have in this column.

Of course keeping

@Gender varchar(50) as you had before.  You probably need

@Gender varchar(10)

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

Select dbo.fx_avgVacationHours('Male')

Open in new window


this post has been revised
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Soumi ChakrabortyAuthor Commented:
Thank you so much eghtebas. This time it worked and now I understand the logic as well.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.