How to reference an input in SQL functions?

Question

I have a table like this:

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

I am writing a function with mySQL to get the n th largest value in Salary. Here is the function:

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
    # Write your MySQL query statement below.
    select DISTINCT Salary 
    FROM Employee 
    ORDER BY Salary  DESC
    LIMIT 1 offset (N - 1)
    #FETCH NEXT 1 ROWS ONLY
    
  );
END

But I got the an error near (N-1).

if I change (N-1) to 1 :

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
    # Write your MySQL query statement below.
    select DISTINCT Salary 
    FROM Employee 
    ORDER BY Salary  DESC
    LIMIT 1 offset 1
    #FETCH NEXT 1 ROWS ONLY
    
  );
END

It runs correctly. So the question is how to reference input in SQL function? It seems it can be directly called as an argument as we do in other languages.

I get the error is not a problem description. What is the error you're seeing? What is the exact error message you're seeing? — Ken White
– Ken White, Commented Sep 22, 2020 at 4:23
@KenWhite it seems to be a syntax error. It just says it's near (N-1). No other specific information...I am using the SQL panel on leetcode. — Xin Niu
– Xin Niu, Commented Sep 22, 2020 at 4:25

Akina · Accepted Answer · 2020-09-22 04:53:25Z

1

LIMIT argument cannot be a variable. Use prepared statement - in it the LIMIT parameter may be taken from a variable. But dynamic SQL is not allowed in the function - use stored procedure:

CREATE PROCEDURE getNthHighestSalary(N INT)
BEGIN
    SET @sql := 'SELECT DISTINCT Salary 
                 INTO @output
                 FROM Employee 
                 ORDER BY Salary  DESC
                 LIMIT 1 OFFSET ?';
    PREPARE stmt FROM @sql;
    SET @output := N-1;
    EXECUTE stmt USING @output;
    DROP PREPARE stmt;
    SELECT @output;
END

fiddle

answered Sep 22, 2020 at 4:53

Akina

43.2k6 gold badges17 silver badges30 bronze badges

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

1 Comment

Xin Niu Over a year ago

Thanks for your answer, unfortunately, I am working on the leetcode problem, which requires me to write a function...But thanks anyway!

Xin Niu · Accepted Answer · 2020-10-05 21:03:46Z

1

It seems I need to declare and set a variable before reference it in SQL query:

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE M INT;
SET M=N-1;
  RETURN (
    # Write your MySQL query statement below.
    select DISTINCT Salary 
    FROM Employee 
    ORDER BY Salary  DESC
    LIMIT 1 OFFSET M
    #FETCH NEXT 1 ROWS ONLY 
  );
END

answered Oct 5, 2020 at 21:03

Xin Niu

6451 gold badge7 silver badges18 bronze badges

Collectives™ on Stack Overflow

How to reference an input in SQL functions?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related