return null in mysql procedure with dynamic query

User-defined variable @get_exam_id doesn't appear to be assigned a value anywhere. If that's not assigned a non-NULL value, then the result of evaluating this line:

set @exam_id = concat( ' and  b.exam_ID = " ', @get_exam_id , '"' );

is that @exam_id will be NULL. And that will cascade into the next CONCAT, which will also evaluate to NULL.

User-defined variables start with an @ at sign character.

Those are distinct and separate from procedure arguments and variables, which do not start with an @ at sign character.

Looks like maybe the intent was to reference the local variable get_exam_id, not a user-defined variable.

FOLLOWUP

Personally, if I had to write the body of the procedure, I'd do it something like this:

DELIMITER $$
CREATE PROCEDURE proc_studymaterial_listing( get_exam_id INT(11))
BEGIN
   SET @sql = CONCAT( 'SELECT c.content_type'
              ,'\n'  ,'     , c.title'
              ,'\n'  ,'     , c.status' 
              ,'\n'  ,'     , c.created_on' 
              ,'\n'  ,'     , c.pdf_is_downloadable' 
              ,'\n'  ,'     , a.content_id' 
              ,'\n'  ,'     , a.expiry_date' 
              ,'\n'  ,'     , b.exam_name'
              ,'\n'  ,'     , fun_subject_name(a.subject_id)  AS subject_name'
              ,'\n'  ,'     , fun_subject_name(a.chapter_id)  AS chapter_name'
              ,'\n'  ,'  FROM tbl_studymaterial_exam_chapter_map a' 
              ,'\n'  ,'  JOIN tbl_exam_master b'
              ,'\n'  ,'    ON b.exam_id = a.exam_id'
              ,'\n'  ,'  JOIN tbl_studymaterial c'
              ,'\n'  ,'    ON c.id = a.content_id'
              ,'\n'  ,' WHERE c.status = 1'
              ,'\n'
              ,CASE
                 WHEN get_exam_id <> 0
                 THEN '   AND b.exam_id = ? '
                 ELSE ''
               END
              ,'\n'  ,' ORDER BY subject_name, chapter_name, exam_name'
              );
   PREPARE stmt FROM @sql;
   IF (get_exam_id <> 0) THEN
      SET @id = get_exam_id;
      EXECUTE stmt USING @id;
      SET @id = '';
   ELSE
      EXECUTE stmt;
   END IF;
   DEALLOCATE PREPARE stmt;
   SET @sql = '';
END;
$$
DELIMITER ;