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