CODEWITHSUNDEEP
mysqlstored-proceduresparameters
user1633947
user1633947

Reputation: 149

MySQL Stored Procedure Not Returning Results, IN Parameters Return NULL

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;

Upvotes: 0

Views: 1528

Answers (1)

Ravinder Reddy
Ravinder Reddy

Reputation: 23982

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 ;

Upvotes: 2

Related Questions