Error in creating function in sql

Question

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.

Damien_The_Unbeliever · Accepted Answer · 2013-04-17 07:24:21Z

1

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.

answered Apr 17, 2013 at 7:24

Damien_The_Unbeliever

241k28 gold badges358 silver badges470 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

muhmud · Accepted Answer · 2013-04-17 07:18:38Z

0

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

answered Apr 17, 2013 at 7:18

muhmud

4,6242 gold badges20 silver badges24 bronze badges

6 Comments

user2289490 Over a year ago

I am able to built function successfully, but now when I tried to execute this by using: SELECT * FROM SecondHighestSalary() It is giving error: Invalid object name 'SecondHighestSalary'. Please suggest.

muhmud Over a year ago

You defined this in the Salary schema. Also, you have defined a scalar function, not a table-valued one, so your call syntax needs to be different. Try running select Salary.SecondHighestSalary()

user2289490 Over a year ago

No, I changed some syntax while creating my function: CREATE FUNCTION SecondHighestSalary() This is my function name and now no schema I am using. And if I call SELECT SecondHighestSalary() it gives the following error: 'SecondHighestSalary' is not a recognized built-in function name.

Damien_The_Unbeliever Over a year ago

@user2289490 - you have to include the schema name when calling a scalar UDF. So if it's no longer in Salary, it's presumably in dbo.

user2289490 Over a year ago

Exactly you are right, I just need to write SELECT dbo.SecondHighestSalary(). But the strange thing is that I have an schema name as Salary, so when I tried using statement CREATE FUNCTION Salary.SecondHighestSalary(), it gives the following error The specified schema name "Salary" either does not exist or you do not have permission to use it.. Can you please suggest where I am doing wrong.

|

Collectives™ on Stack Overflow

Error in creating function in sql

2 Answers 2

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related