MySQL Stored Procedure Not Returning Results, IN Parameters Return NULL

Question

I am using MyPhpAdmin 4.2 visual editor. When I call the stored procedure and pass the parameter values, I do not get any rows returned and outputting the value of the parameters shows null values.

SET @p0='1'; SET @p1='1'; SET @p2='41'; SET @p3='1'; CALL `ps_stp_getPrograms`(@p0, @p1, @p2, @p3);

BEGIN 
  CREATE TEMPORARY TABLE temp_progs(prog_id bigint(20));
  IF @countyID > 0 THEN                    
    INSERT INTO temp_progs (prog_id)
    SELECT 1 from ps_prog_county 
    WHERE ps_prog_county.county_id = @countyID AND ps_prog_county.prog_id = @progID;                  
  END IF;  
  IF @ServiceID > 0 THEN 
    INSERT INTO temp_progs (prog_id)
    SELECT 1 from ps_prog_services 
    WHERE ps_prog_services.svc_id = @ServiceID AND ps_prog_services.prog_id = @progID;
  END IF; 
  IF @popID > 0 THEN   
    INSERT INTO temp_progs (prog_id)
    SELECT 1 from ps_prog_served
    WHERE ps_prog_served.served_id = @popID AND ps_prog_served.prog_id = @progID;
  END IF; 
SELECT DISTINCT prog_id FROM temp_progs;
SELECT @progID, @countyID, @ServiceID, @popID;
END

If I set the values it returns as expected

SET @progID = 1;
SET @countyID = 1;
SET @ServiceID = 41;
SET @popID = 1;

Ravinder Reddy · Accepted Answer · 2014-06-02 18:38:46Z

2

You have to use the same names of parameters of a stored procedure within its body.

When you use user variables they are not same as SP in parameters.

Example:

delimiter //
create procedure 
  ps_stp_getPrograms(
    IN progID int
    , IN countyID int
    , IN ServiceID int
    , IN popID int
  )
BEGIN 
  CREATE TEMPORARY TABLE temp_progs( prog_id bigint(20) );
  IF countyID > 0 THEN                    
    INSERT INTO temp_progs( prog_id )
      SELECT 1 from ps_prog_county 
       WHERE ps_prog_county.county_id = countyID 
         AND ps_prog_county.prog_id = progID;                  
  END IF;  
  IF ServiceID > 0 THEN 
    INSERT INTO temp_progs ( prog_id )
      SELECT 1 from ps_prog_services 
       WHERE ps_prog_services.svc_id = ServiceID 
         AND ps_prog_services.prog_id = progID;
  END IF; 
  IF popID > 0 THEN   
    INSERT INTO temp_progs ( prog_id )
      SELECT 1 from ps_prog_served
       WHERE ps_prog_served.served_id = popID 
         AND ps_prog_served.prog_id = progID;
  END IF; 

  SELECT DISTINCT prog_id FROM temp_progs;
  SELECT progID, countyID, ServiceID, popID;

END; 
//
delimiter ;

edited Jun 2, 2014 at 18:38

answered Jun 2, 2014 at 18:19

Ravinder Reddy

24.1k6 gold badges54 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1633947 Over a year ago

Thank you, the simplest things can get you off track sometimes.

Collectives™ on Stack Overflow

MySQL Stored Procedure Not Returning Results, IN Parameters Return NULL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related