SQL: How to convert column value into sql operator?-CodePudding

Here is the code, on the last part where i typed ( opr ) gives an error. Is it possible to convert the opr data value to make it work as > or <?

CREATE TABLE students (
  name TEXT PRIMARY KEY,
  x INTEGER NOT NULL,
  opr varcharacter NOT NULL
);

INSERT INTO students VALUES ('Ryan', 15,'<');
INSERT INTO students VALUES ('Joanna', 15,'>');

SELECT * FROM students WHERE x   opr   4;

CodePudding user response：

you can use case inside your where clause

SELECT * FROM students 
WHERE
CASE  opr
when '>' then x>4
when '<' then x<4
end

CodePudding user response：

You can use Dynamic SQL

CREATE TABLE students (id INTEGER,name varchar(100) PRIMARY KEY,x INTEGER,opr varchar(1));
INSERT INTO students VALUES (1, 'Ryan', 15,'<');
INSERT INTO students VALUES (2, 'Joanna', 15,'>');
DELIMITER $$
CREATE PROCEDURE CalculateStudents (_id INTEGER, y INTEGER)
BEGIN
    select @query := CONCAT('SELECT * from students where ',x,' ',opr,' ',y,';')
    from students 
    where id = _id;
    PREPARE prepared_statement FROM @query;
    EXECUTE prepared_statement;
    DEALLOCATE PREPARE prepared_statement; 
END$$
DELIMITER ;

Call the procedure

CALL CalculateStudents(1,4); not return anything;

CALL CalculateStudents(2,4); returns all;