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

portlightAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

portlightAuthor Commented:
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

0
portlightAuthor Commented:
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

0
mlmccCommented:
Should this part use the parameter names?

    /*
      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;
      

Open in new window


    /*
      Sets the variables to use for the SELECT query
    */
      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

     

Also you are passing in 1 for the logId and GroupId.  Should that be -1 or is there a logId of 1?

mlmcc
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ZberteocCommented:
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

0
ZberteocCommented:
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?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.