CODEWITHSUNDEEP
sql-serversql-server-2008
user2289490
user2289490

Reputation: 747

Error in creating function in sql

CREATE FUNCTION Salary.SecondHighestSalary()

Returns int

AS 

    BEGIN
    Declare @salary int;

  SELECT TOP 1 salary FROM (

  SELECT TOP 2 salary FROM Salary
  ORDER BY salary DESC
) as maxsalary ORDER BY salary ASC

Return @salary  
END

Hello everyone, I am trying to create a sql function of getting the second highest salary by using the above syntax but i am getting the error:

"Select statements included within a function cannot return data to a client."

Can anyone please tell me what is the actual syntax to write the function or just turn my code back with the correct syntax. Thanks in advance.

Upvotes: 2

Views: 103

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239664

I'd write is using ROW_NUMBER() as follows:

CREATE FUNCTION SecondHighestSalary()

Returns int

AS 
begin
    Return (
      select salary from (
         select salary,ROW_NUMBER() OVER (ORDER BY salary desc) as rn
         from Salary
        ) t
      where rn = 2);
END

This also allows it to be easily adapted if you need to deal with e.g. ties (switch to RANK or DENSE_RANK as appropriate) which the TOP and ORDER BY approach isn't so amenable to.

Upvotes: 1

muhmud
muhmud

Reputation: 4604

CREATE FUNCTION Salary.SecondHighestSalary()

Returns int

AS 

    BEGIN
    Declare @salary int;

  SELECT TOP 1 @salary = salary FROM (

  SELECT TOP 2 salary FROM Salary
  ORDER BY salary DESC
) as maxsalary ORDER BY salary ASC

Return @salary  
END

Upvotes: 0

Related Questions