CODEWITHSUNDEEP
mysqlfunction
Xin Niu
Xin Niu

Reputation: 635

How to reference an input in SQL functions?

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). enter image description here

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.

Upvotes: 0

Views: 822

Answers (2)

Xin Niu
Xin Niu

Reputation: 635

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

Upvotes: 1

Akina
Akina

Reputation: 42854

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

Upvotes: 1

Related Questions