procedure is not executing

Question

Problem I am getting when I execute this proc:

call `sp_frm4_px_prc_calc`();

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1

The procedure is:

SHOW CREATE PROCEDURE sp_frm4_px_prc_calc\G

CREATE DEFINER=`power`@`%` PROCEDURE `sp_frm4_px_prc_calc`()
BEGIN
DECLARE _id INT(5);
DECLARE _SLLR_ID INT(4);
DECLARE _SLLR_STT_ID INT(4);
DECLARE _BYR_ID INT(4);
DECLARE _BYR_STT_ID INT(4);
DECLARE _strt_dt DATE; 
DECLARE _end_dt DATE ;
DECLARE _strt_tm TIME;
DECLARE _end_tm TIME;
DECLARE _exchng VARCHAR(20);
DECLARE _stt VARCHAR(250);
DECLARE _st_tm_blck INT(2);
DECLARE _end_tm_blck INT(2);
DECLARE _avg_px_prc DOUBLE(4,2);
DECLARE _done INT(1) DEFAULT 0;
DECLARE _px_rgn_id SMALLINT(2);
DECLARE cur1 CURSOR FOR 
  SELECT ID,SLLR_ID,SLLR_STT_ID,BYR_ID,BYR_STT_ID,STRT_DT,END_DT,STRT_TM,END_TM 
  FROM frm4 
  WHERE TRNS_TYP_ID IN(4,7,10) AND ( avg_px_prc=0.00 OR avg_px_prc IS NULL);
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET _done=1;
OPEN cur1;
REPEAT
FETCH cur1 INTO _id,_SLLR_ID,_SLLR_STT_ID,_BYR_ID,_BYR_STT_ID,_strt_dt,_end_dt,_strt_tm,_end_tm;
-- Finding which column has Exchange Value;
IF (_SLLR_ID IN (SELECT ID FROM entty_frm4 WHERE SHRT_NM IN ('IEX')) AND _BYR_STT_ID IN (SELECT ID FROM entty WHERE ctgry_id IN (1,2))) THEN 
    SET _exchng=1,_stt=_BYR_STT_ID;
ELSEIF  (_SLLR_ID IN (SELECT ID FROM entty_frm4 WHERE SHRT_NM IN ('PXI','PXIL')) AND _BYR_STT_ID IN (SELECT ID FROM entty WHERE ctgry_id IN (1,2))) THEN
    SET _exchng=2,_stt=_BYR_STT_ID;
ELSEIF  (_BYR_ID IN (SELECT ID FROM entty_frm4 WHERE SHRT_NM IN ('IEX')) AND _SLLR_STT_ID IN (SELECT ID FROM entty WHERE ctgry_id IN (1,2))) THEN
    SET _exchng=1,_stt=_SLLR_STT_ID; 

ELSEIF  (_BYR_ID IN (SELECT ID FROM entty_frm4 WHERE SHRT_NM IN ('PXI','PXIL')) AND _SLLR_STT_ID IN (SELECT ID FROM entty WHERE ctgry_id IN (1,2))) THEN
    SET _exchng=2,_stt=_SLLR_STT_ID; 
ELSE 
    SET _exchng=0,_stt=0;
END IF;
IF (_exchng<>0 AND _stt<>0 ) THEN
SELECT MIN(TM_BLCK),MAX(TM_BLCK) INTO _st_tm_blck,_end_tm_blck FROM tm_blck_mstr WHERE STRT_TM>=_strt_tm AND END_TM<=_end_tm;
-- SELECT _id,_exchng,_stt,_strt_dt,_end_dt,_st_tm_blck,_end_tm_blck;
SELECT px_rgn_id INTO _px_rgn_id FROM stt_px_rgn WHERE stt_id=_stt AND exchng_id=_exchng;
 -- SELECT CONCAT("Before Change : ",_px_rgn_id," ",_strt_dt," ",_exchng);
IF (_px_rgn_id IN (7,12)) THEN
    IF (_px_rgn_id=7 AND _exchng=1 AND _strt_dt<'2011-08-31' ) THEN 
    SET _px_rgn_id=5;
        ELSEIF (_px_rgn_id=12 AND _exchng=1 AND _strt_dt<'2011-09-02' ) THEN 
        SET _px_rgn_id=10;
    ELSEIF (_px_rgn_id=7 AND _exchng=2 AND _strt_dt<'2011-08-31' ) THEN 
    SET _px_rgn_id=5;
    ELSEIF (_px_rgn_id=12 AND _exchng=2) THEN 
    SET _px_rgn_id=10;

    END IF;
END IF;
 -- SELECT CONCAT("After Change : ",_px_rgn_id," ",_strt_dt," ",_exchng);
SET @_query=CONCAT("SELECT ROUND(AVG(vl)/1000,2) into @_avg_px_prc FROM px_prc_tb a  where a.px_rgn_id=",_px_rgn_id," and  a.exchng_id=",_exchng," AND a.dt BETWEEN '",_strt_dt,"' AND '",_end_dt,"' AND a.tm_blck BETWEEN ",_st_tm_blck," AND ",_end_tm_blck,";");
PREPARE stmt FROM @_query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- select @_query,@_avg_px_prc;
    IF (@_avg_px_prc IS NOT NULL) THEN
        SET @_queryUpdatePxPrc=CONCAT("update frm4 set avg_px_prc=",@_avg_px_prc," where id=",_id,";");
        PREPARE stmtUpdatePxPrc FROM @_queryUpdatePxPrc;
        EXECUTE stmtUpdatePxPrc;
        DEALLOCATE PREPARE stmtUpdatePxPrc;
        -- SELECT @_queryUpdatePxPrc;
    ELSE
        SET @_queryUpdateErr=CONCAT("INSERT INTO `indiaelectron`.`err`  (`PRGRM`,`MSG`) VALUES  ('sp_frm4_px_prc_calc','Data not found in px_prc_tb for Row : ",_id,"');");
        PREPARE stmtUpdateErr FROM @_queryUpdateErr;
        EXECUTE stmtUpdateErr;
        DEALLOCATE PREPARE stmtUpdateErr;
        -- select @_queryUpdateErr;
    END IF ;
 ELSE
SET @_queryUpdateErr=CONCAT("INSERT INTO `indiaelectron`.`err`  (`PRGRM`,`MSG`) VALUES  ('sp_frm4_px_prc_calc','Exchange or State is missing at Row : ",_id,"');");
PREPARE stmtUpdateErr FROM @_queryUpdateErr;
EXECUTE stmtUpdateErr;
DEALLOCATE PREPARE stmtUpdateErr;
-- SELECT @_queryUpdateErr;
END IF;
 UNTIL _done=1 END REPEAT;
CLOSE cur1;
END

Answer 1

There are several places in your code where you declare prepared statements using query strings you're crafting with CONCAT(). If any of the arguments to CONCAT() is NULL, the function's return value is also NULL... so it looks like at some point you're unintentially doing (effectively) this:

mysql> SET @oops = NULL;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @oops;
+-------+
| @oops |
+-------+
| NULL  |
+-------+
1 row in set (0.00 sec)

mysql> PREPARE foo FROM @oops;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1

Same error.

So the error is originally occurring because there's a null value, probably unanticipated, in one of the variables you're using to craft the string you're passing to one of the PREPARE statements... forcing the whole value returned from CONCAT() to be null.

However, before you go chasing those nulls with tests and IFNULL(), I want to add that my answer is incomplete unless I point out that you have another problem, or two (efficiency and safety) that you can fix at the same time.

You can, and probably should, call PREPARE for all of your different statements before opening the cursor, with ? placeholders for the values you need to pass in from variables. Later:

EXECUTE stmt USING @var1, @var2, @var3; # using the appropriate variables.

Otherwise, you're continually allocating and deallocating resources unnecessarily, losing efficiency in the process (and chasing down errors like this one). You can deallocate at the end of the proc.

Using string concatenation to craft prepared statements... or any queries... in any environment... should be avoided unless there is no alternative. SQL injection attacks would be virtually impossible if this practice weren't still so commonplace. It's not safe. Of course, it still works, which is exactly why it's so dangerous.

The placeholders in prepared statements should not be thought of as simply a shortcut or convenience -- they also maintain and enforce the boundary between what is the query and what are the arguments (not to mention allowing the server to parse the prepared statement only once, making each execution faster than if the whole thing is torn down and rebuilt each time you need it). You can't pass anything as an argument to a prepared statement that the server will misinterpret as part of your query. You can easily do this unintentionally with concatenation.

http://dev.mysql.com/doc/refman/5.5/en/sql-syntax-prepared-statements.html

ETA: The arguments passed with USING have to be "user" variables like @var1, not "local" variables like var1, declared inside the proc, because prepared statements are scoped to the session and because of statement-based replication, whether you're using it or not. An interesting side effect of this is that if you need to call the same prepared statement in a proc called by another proc (or in a proc you call recursively), you don't have to prepare it again in the inner proc -- it's still legit... and you will actually have a small performance hit for re-declaring it. In fact, a prepared statement that you don't DEALLOCATE is actually still legit from your session after the stored procedure's execution is finished.