Can anyone please let me know where of the SQL query is wrong?
The error is
Incorrect syntax near "return"
Code:
CREATE FUNCTION getNthHighestSalary(@N INT)
RETURNS INT
AS
BEGIN
WITH ranksalary AS
(
SELECT
salary,
ROW_NUMBER() OVER (ORDER BY Salary DESC) AS Rank
FROM
Employee
)
RETURN (SELECT Salary AS getNthHighestSalary
FROM ranksalary
WHERE Rank = @N);
END
Looks like T-SQL to me. As @marc_s already pointed out you need to store the value in a variable first and then return that.
CREATE FUNCTION getNthHighestSalary(@N INT)
RETURNS INT
AS
BEGIN
DECLARE @result int
;WITH ranksalary AS
(
SELECT
salary,
ROW_NUMBER() OVER (ORDER BY Salary DESC) AS [Rank]
FROM
Employee
)
SELECT @result = Salary
FROM ranksalary
WHERE [Rank] = @N
RETURN @result
END
GO
Why not just use this logic?
declare @rank;
select @rank = count(*) + 1
from employee
where salary > @salary;
return @rank;
SELECTinto a local variable in your function, and then return that variable's value.