Can anyone point out the mistake?-CodePudding

Logic for creating the function

If the employee has completed one year from the date which he is hired, salary has to be incremented by 1k.
Like that for every year completion, salary has to increment by 1k.

CodePudding user response：

If I understand you correct you want a function that will calculate the new salary of employees based on the hiredate and the current date

A function like that can look like this

create function EmployeeSalaryIncrement(@hiredate date, @sal int) returns int as
begin
     declare @years int = datediff(year, @hiredate, getdate())
     return @sal   (@years * 1000)
end

Please find a complete example here, check it out if this is what you need

CodePudding user response：

Considering that the calculation is a direct calculation on data from a single table an Inline Table Valued Function (iTVF) might yield better performance. Normally this is the case.

As such the function can be:

CREATE FUNCTION dbo.tvf_EmployeeSalaryIncrement( @EmplCode int )
RETURNS TABLE
AS
RETURN (  
  SELECT
    e.EMPNO
  , Datediff( year, e.HIREDATE, Sysdatetime() ) * 1000
      e.SAL AS CurrentSalary
  FROM
    EMP AS e
  WHERE
      e.EMPNO = @EmplCode
);

This can be used like:

SELECT
  ...
FROM EMP AS e
  INNER JOIN tvf_EmployeeSalaryIncrement( e.EMPNO )
;

In all honesty using a function just because there is a calculation seems overkill to me. A much simpler way is a simple view that takes the columns from table EMP and adds the calculation (as single line) to the output. This works unless the date to use as comparison date shall be flexible. In this case this comparison date can be added to the function.

Additional background for Table Valued Functions:

https://www.sqlservercentral.com/articles/the-basics-of-itvfs