Comparison Operators in stored procedures SQL

user_12201366 0
Viewed 188

Hope you can help.

I have this stored procedure. Works fine, but I have problems using comparison operators in my stored procedure. The point here is to find cars who been driving above 10000.

I have been looking for the answer, but for some reason, can't find the solution.

elimiter //

CREATE PROCEDURE DISTANCE (kilometerafstand INT)
BEGIN
    SELECT bil.registreringsnummer, bil.kilometerstand, biltype.maerke, biltype.model
    FROM bil
    INNER JOIN biltype ON bil.id = biltype.id 
    WHERE bil.kilometerstand = kilometerafstand;
END;

//

call DISTANCE ( > 10000)

The code works fine. It's just the operators in my call DISTANCE.

Thank you!

comparison-operators stored-procedures sql
2 Answers

you can't have comparison operator like this. You can have one more parameter

 operator varchar

and you can have custom logic like eq, ne, lt, gt, vice versa. and you can write

where (operator = 'eq' and kilometerstand = kilometerafstand )
  or (operator = 'gt' and kilometerstand > kilometerafstand
  or ....
user_11189780

I would just devise the stored procedure to take two parameters, a minimum and maximum:

DELIMITER //

CREATE PROCEDURE DISTANCE (in_min_km INT, in_max_km INT)
BEGIN
    SELECT bil.registreringsnummer, bil.kilometerstand, biltype.maerke, biltype.model
    FROM bil
    INNER JOIN biltype ON bil.id = biltype.id 
    WHERE (bil.kilometerstand >= in_min_km or in_min_km IS NULL) AND
          (bil.kilometerstand <= out_min_km or in_min_km IS NULL);          
END;

//

CALL DISTANCE(10000, NULL) ;
user_11440350
Related