Link to home
Start Free TrialLog in
Avatar of portlight
portlightFlag for United States of America

asked on

creating stored proc

I need some quick refreshers on creating stored procs in mysql. I have a stored procedure that I need to create if elses or case scenarios in. I am accustom to working on ms sql but the syntax is different in mySql.

I am passing two variable into the query: groupID and logID

IF groupID does not = -1 then I need to set it as groupID - if it does equal -1 i need to set it as all (like '%')

The same with logID
   IF logID does not = -1 then I need to set it as logID - if it does equal -1 i need to set it as all (like '%')

I am also going to need to pass in startDate and endDates - these are passed to me as a string so I am thinking in can just call them varchar(50)

This is what I have so far (I have not declared any variables as I was not sure that was needed)

DELIMITER //
/*  Author: Jessie Brown     
    Date:   8/17/13 
    Notes:  Template Creation

    Comments: Comments created in the header area will NOT be stored in the database write
              Comments created After the begin statement will be retained within the database write
*/

-- checks to see if stored procedure exists
-- if exists the procedure is dropped and recreated

DROP PROCEDURE IF EXISTS `reportLogExceptions`//

-- Creates new procedure

CREATE PROCEDURE reportLogExceptions (IN logId varchar(50),IN groupId varchar(50))
    BEGIN
      /*
        report name:Log Exceptions
        Test Query: CALL reportLogExceptions('1','1')
      */
 SELECT g.groupId,g.name AS groupName,l.logId,l.name AS logName, i.itemID,
  i.name AS itemName, le.userName,completed, i.optimalMin,i.optimalMax ,value
  FROM groups g
    JOIN LOGS l ON g.groupID = l.groupId
    JOIN logExceptions le ON l.logID = le.logID
    JOIN items i ON l.logId = i.logId
  WHERE  l.logID = logId
    AND g.groupID = groupId 
    AND l1.COMPLETED
      BETWEEN FROM_UNIXTIME($P{fromDate} / 1000)
      AND FROM_UNIXTIME ($P{toDate} / 1000)
  END //

DELIMITER//

Open in new window

Avatar of portlight
portlight
Flag of United States of America image

ASKER

Updated Code


DELIMITER //
/*  Author: Jessie Brown     
    Date:   8/17/13 
    Notes:  Template Creation

    Comments: Comments created in the header area will NOT be stored in the database write
              Comments created AFTer the begin statement will be retained within the database write
*/

-- checks to see if stored procedure exists
-- if exists the procedure is dropped and recreated

DROP PROCEDURE IF EXISTS `reportLogExceptions`//

-- Creates new procedure

CREATE PROCEDURE reportLogExceptions 
  (IN logId varchar(50),
    IN groupId varchar(50),
    IN startDate varchar (50),
    IN endDate varchar(50))
    BEGIN
      /*
        report name:Log Exceptions
        Test Query: CALL reportLogExceptions('1','1')
      */
 SELECT g.groupId,g.name AS groupName,l.logId,l.name AS logName, i.itemID,
  i.name AS itemName, le.userName,completed, i.optimalMin,i.optimalMax ,value
  FROM groups g
    JOIN LOGS l ON g.groupID = l.groupId
    JOIN logExceptions le ON l.logID = le.logID
    JOIN items i ON l.logId = i.logId
  WHERE  l.logID LIKE logId
    AND g.groupID LIKE groupId 
    AND l1.COMPLETED
      BETWEEN FROM_UNIXTIME(startDate / 1000)
      AND FROM_UNIXTIME (endDate / 1000);
  END //

DELIMITER//

Open in new window

I now have the query compiling with no errors BUT it is NOT returning any data as I suspected.
What advice?
DELIMITER //
/*  Author: Jessie Brown     
    Date:   8/17/13 
    Notes:  Template Creation

    Comments: Comments created in the header area will NOT be stored in the database write
              Comments created AFTer the begin statement will be retained within the database write
*/

-- checks to see if stored procedure exists
-- if exists the procedure is dropped and recreated

DROP PROCEDURE IF EXISTS `reportLogExceptions`//

-- Creates new procedure

CREATE PROCEDURE reportLogExceptions (IN logId varchar(50),
    IN groupId varchar(50),
    IN fromDate varchar (50),
    IN toDate varchar(50))

  BEGIN
      /*
        report name: Log Exceptions
        Test Query: CALL reportLogExceptions('1','1','1378385650','1378472050')
      --------------------------------------------------------------------------
        Declare variables*/

      DECLARE logInS varchar(50);
      DECLARE groupIdS varchar (50);

    /*
      Sets the variables to use for the SELECT query
    */
      CASE
        WHEN logInS = '-1' THEN
        SET logInS = ''%'';
         ELSE SET logInS = logInS;
      END CASE;

      CASE
        WHEN groupIdS = '-1' THEN
        SET groupIdS = ''%'';
          Else SET groupIdS = groupIdS;   END Case;
      
  SELECT g.groupId,g.name AS groupName,l.logId,l.name AS logName, i.itemID,
      i.name AS itemName, le.userName,completed, i.optimalMin,i.optimalMax ,value
  FROM groups g
      JOIN LOGS l ON g.groupID = l.groupId
      JOIN logExceptions le ON l.logID = le.logID
      JOIN items i ON l.logId = i.logId
  WHERE  l.logID LIKE logInS
      AND g.groupID LIKE groupIdS 
      AND le.completed
        BETWEEN FROM_UNIXTIME(fromDate / 1000)
        AND FROM_UNIXTIME (toDate / 1000);
  END//

DELIMITER ;

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Mike McCracken
Mike McCracken

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You don't need 2 single quotes around % but one:

    */
      CASE
        WHEN logInS = '-1' THEN
        SET logInS = '%';
         ELSE SET logInS = logId;
      END CASE;

      CASE
        WHEN groupIdS = '-1' THEN
        SET groupIdS = '%';
          Else SET groupIdS = groupId;   
      END Case;

Open in new window

I didn't come up with the solution but I pointed out the issue with the code that wasn't working. A points split would have worked here. Or am I missing something?