Task in Stored Procedure - Solution

SQL
1

Hi Everyone,

I am doing SQL mastery course, which is great. At the moment, I am learning how stored procedures are created. I had an exercise with following requirements:

  • Write a stored procedure called get_payments
    – with two parameters
    – client_id INT(4)
    – payment_method_id TINYINT(1) (It can take values 0 to - 255)
    – Both parameters should be optional
    – passing 0 for both of them should return all the payments in the database
    – if you supply client id it should return payments for this client
    – if we set both of these parameters it should return all the payments for a given client using the given payment_method_id

My solution: Is it a valid solution ??

DELIMITER $$

CREATE PROCEDURE get_paymentsold
(
client_id INT,
payment_method TINYINT
)
BEGIN
IF client_id IS NULL AND payment_method IS NULL THEN
SELECT * FROM payments;
ELSE
SELECT * FROM payments p
WHERE p.client_id = client_id OR p.payment_method = payment_method;
END IF;
END$$

Thanks,
Davit