portlight
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)
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//
ASKER
I now have the query compiling with no errors BUT it is NOT returning any data as I suspected.
What advice?
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 ;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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;
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?
ASKER
Open in new window