Creating a UDF with return value based on parameter

Question

I'm getting this error and not sure what to do. Also, how can I return the current salary based on the parameter @empId. For instance, if I call the function like dbo.getBonus(3) it should return: 5600.000

Here is the table that is created when I run the query:

EmpId  Bonus
3   5600.000
4   18500.000
5   15600.000
6   7600.000

Error Message:

Msg 444, Level 16, State 2, Procedure getBonus, Line 188 Select statements included within a function cannot return data to a client.

Thanks.

CREATE FUNCTION dbo.getBonus(@empId INT)
RETURNS numeric(10,2)
AS
BEGIN
SELECT C.[Emp Id], 
    (Salary.Emp_Salary * .1) AS 'Bonus'
        FROM
        (
        SELECT
            e.Emp_Id AS "Emp Id",
            MAX(Emp_Salary_Change_Year) AS "Change Year"
            FROM Employee_Details AS e

        INNER JOIN Country AS co ON e.Emp_Country_Id = co.Country_Id
        INNER JOIN State AS s ON e.Emp_State_Id = s.State_Id
        INNER JOIN Designation AS d ON e.Desig_Id = d.Desig_Id
        INNER JOIN Salary AS sa ON e.Emp_Id = sa.Emp_Id

        GROUP BY e.Emp_Id) AS C
        INNER JOIN Salary ON C.[Change Year] =          
Salary.Emp_Salary_Change_Year;
    RETURN 2
END

artm · Accepted Answer · 2016-11-07 05:39:54Z

3

Put the result to a variable and return the result:

CREATE FUNCTION dbo.getBonus(@empId INT)
RETURNS numeric(10,2)
AS
BEGIN
DECLARE @res numeric(10, 2)
SELECT @res = 
    (Salary.Emp_Salary * .1) 
        FROM
        (
        SELECT
            e.Emp_Id AS "Emp Id",
            MAX(Emp_Salary_Change_Year) AS "Change Year"
            FROM Employee_Details AS e

        INNER JOIN Country AS co ON e.Emp_Country_Id = co.Country_Id
        INNER JOIN State AS s ON e.Emp_State_Id = s.State_Id
        INNER JOIN Designation AS d ON e.Desig_Id = d.Desig_Id
        INNER JOIN Salary AS sa ON e.Emp_Id = sa.Emp_Id

        GROUP BY e.Emp_Id) AS C
        INNER JOIN Salary ON C.[Change Year] =          
Salary.Emp_Salary_Change_Year;
    RETURN @res
END

answered Nov 7, 2016 at 5:39

artm

8,5943 gold badges28 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alanah.Hendricks Over a year ago

thanks, but no matter what id parameter i put in, it always returns 7600.00.

Nayak Over a year ago

Just add a WHERE clause in the function for returning the output as per the input parameter - @empId :)

Collectives™ on Stack Overflow

Creating a UDF with return value based on parameter

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related