User Defined Function

Soumi Chakraborty
Soumi Chakraborty used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mike EghtebasDatabase and Application Developer

Commented:
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

Author

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.
Database and Application Developer
Commented:
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

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial