CODEWITHSUNDEEP
mysqlstored-procedures
lnotik
lnotik

Reputation: 115

Stored Procedure with if statement

I have this stored procedure which is supposed to check whether a field is empty and execute one query, otherwise execute another but I'm getting an error. Here is the code:

DELIMITER $$
 CREATE PROCEDURE mw_info(
    m_date_begin INTEGER,
    m_date_end INTEGER,
    m_from INTEGER,
    m_numberOfrecords INTEGER,
    m_callsource VARCHAR(15))
 BEGIN

    IF (m_callsource = '') THEN

       SELECT * 
         FROM mw_iaa 
        WHERE calllegcode IN ('CB_A','CB_B')
          AND calltime BETWEEN m_date_begin AND m_date_end
        ORDER BY calltime DESC 
        LIMIT m_from, m_numberOfrecords;

    ELSE     

       SELECT * 
         FROM mw_iaa 
        WHERE calllegcode IN ( 'CB_A', 'CB_B')
          AND callsource = m_callsource
          AND calltime BETWEEN m_date_begin AND m_date_end
        ORDER BY calltime DESC 
        LIMIT m_from, m_numberOfrecords;    

    END IF; 

 END;  
 END$$
DELIMITER ;

And this is the error:

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 'else
SELECT * FROM mw_iaa WHERE calllegcode' at line 1

What can the problem be?

Upvotes: 2

Views: 125

Answers (2)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79939

You can do this in only one query, and get rid of the IF statement like so:

SELECT * 
FROM mw_iaa
WHERE ((m_callsource = '') OR (callsource = m_callsource))
  AND calllegcode IN ('CB_A', 'CB_B')
  AND callsource = m_callsource
  AND calltime BETWEEN m_date_begin AND m_date_end
ORDER BY calltime DESC 
LIMIT m_from, m_numberOfrecords;

Then, whenever you pass the m_callsource parameter with '', the condition ((m_callsource = '') OR (callsource = m_callsource)) will be ignored as it was not presented.

Upvotes: 2

Bohemian
Bohemian

Reputation: 425043

I've seen errors like this then I've had tab characters in the procedure source.

Try replacing all tab chars with spaces.

Upvotes: 0

Related Questions