Search code examples
sql-serverfunctionreturn

Creating a UDF with return value based on parameter


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

Solution

  • 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