Fetch value/values from table

Question

I have the following table in my database

CREATE TABLE `sms_pool` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `ag_id` VARCHAR(20) NOT NULL,
    `sms_to` VARCHAR(15) NOT NULL,
    `template_name` VARCHAR(100) NOT NULL,
    `contents` VARCHAR(500) NOT NULL,
    `bulk_flag` VARCHAR(1) NOT NULL,
    `file_name` VARCHAR(100) NULL DEFAULT NULL,
    `send_flag` VARCHAR(1) NOT NULL,
    `creation_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `created_by` VARCHAR(20) NOT NULL,
    `processing_msg` VARCHAR(2000) NULL DEFAULT NULL,
    PRIMARY KEY (`id`),    
);

I wish to write a procedure/function which takes 'id' as input.

If that 'id' is equal to any id in table then it should return the corresponding row,

if 'id' = NULL then it should return all of the rows from the database.

NOTE : if 'id' is not present in table then it should return all of the rows.

How should I do this? Any help is appreciated. Thank you in advance. :D

Answer 1

create procedure sp_get_data(in in_id int)
begin
      declare temp_id int;
      set temp_id =(select count(*) from table_name where id =in_id);
      if(temp_id) >0
      Then 
          select * from table_name where id = in_id ;              
      else 
          select * from table_name ;
      end 
end