Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Calling one stored procedure from another

Posted on 2013-12-19
6
Medium Priority
?
635 Views
Last Modified: 2013-12-19
I am trying to call one stored procedure withing another - using an if statement.
I am getting an error so I believe that I have something out of sequence

CREATE PROCEDURE reportFreeCoolingTrackerCalls (
        IN fromDate varchar (50),
        IN toDate varchar (50),
        IN timeZone varchar (50))

    BEGIN
            DECLARE startDate varchar (50);
            DECLARE endDate varchar (50);
            DECLARE mylogID Int;

             SET startDate = FROM_UNIXTIME(fromDate/1000);
             SET endDate = FROM_UNIXTIME(toDate/1000);
            
     IF (l1.activityId = t2.activityId) 
      THEN CALL reportFreeCoolingTrackerError (
        fromDate,
        toDate,
        timeZone );
    ELSEIF  (l1.activityId != t2.activityId)
       THEN CALL reportFreeCoolingTracker (
        fromDate,
        toDate,
        timeZone );
            END IF;

         
  SELECT l1.activityId,t2.activityId
  FROM logs l 
      INNER JOIN groups g ON g.groupId = l.groupId
      LEFT JOIN groups g1 ON g.parentId = g1.groupId
      LEFT JOIN groups g2 ON g1.parentId = g2.groupId
      LEFT JOIN groups g3 ON g2.parentId = g3.groupId                
      INNER JOIN activities a ON l.logId = a.logId 
      INNER JOIN log1644 l1 ON a.activityId = l1.activityId 
      INNER JOIN log1644 t2 ON t2.recordId = l1.recordid + 1
      INNER JOIN items i ON l.logId = i.logId AND i.name LIKE '%KW%'
      INNER JOIN users u ON l1.userId = u.userId AND i.name LIKE '%KW%'
     WHERE i.itemID = "31985"  AND  l1.activityId = 1257
      AND l1.started
        BETWEEN startDate
            AND endDate 
     ORDER BY l1.recordId,l1.started;
  
    END //

DELIMITER ;

Open in new window


ERROR

Unknown table 'l1' in field list
0
Comment
Question by:portlight
  • 4
  • 2
6 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 39730106
Those aliases, l1 and l2, exist only in the context of the SELECT statement that creates them.
0
 

Author Comment

by:portlight
ID: 39730111
Okay Is there a way to fix this?
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 39730124
This is untested, but should get you a step closer.

CREATE PROCEDURE reportFreeCoolingTrackerCalls (
        IN fromDate varchar (50),
        IN toDate varchar (50),
        IN timeZone varchar (50))

    BEGIN
            DECLARE startDate varchar (50);
            DECLARE endDate varchar (50);
            DECLARE mylogID Int;
            DECLARE @ActivityID1 int;
            DECLARE @ActivityID2 int;

             SET startDate = FROM_UNIXTIME(fromDate/1000);
             SET endDate = FROM_UNIXTIME(toDate/1000);
            

SELECT @ActivityID1 = l1.activityId, @ActivityID2 = t2.activityId
  FROM logs l 
      INNER JOIN groups g ON g.groupId = l.groupId
      LEFT JOIN groups g1 ON g.parentId = g1.groupId
      LEFT JOIN groups g2 ON g1.parentId = g2.groupId
      LEFT JOIN groups g3 ON g2.parentId = g3.groupId                
      INNER JOIN activities a ON l.logId = a.logId 
      INNER JOIN log1644 l1 ON a.activityId = l1.activityId 
      INNER JOIN log1644 t2 ON t2.recordId = l1.recordid + 1
      INNER JOIN items i ON l.logId = i.logId AND i.name LIKE '%KW%'
      INNER JOIN users u ON l1.userId = u.userId AND i.name LIKE '%KW%'
     WHERE i.itemID = "31985"  AND  l1.activityId = 1257
      AND l1.started
        BETWEEN startDate
            AND endDate 
     ORDER BY l1.recordId,l1.started;

     IF (@ActivityID1 = @ActivityID2) 
      THEN CALL reportFreeCoolingTrackerError (
        fromDate,
        toDate,
        timeZone );
    ELSEIF  (@ActivityID1 != @ActivityID2)
       THEN CALL reportFreeCoolingTracker (
        fromDate,
        toDate,
        timeZone );
            END IF;

         
  Select @ActivityID1, @ActivityID2;
  
    END //

DELIMITER ;

Open in new window

0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 39730126
Basically, I'm selecting the values you need, sticking them into variables, then calling the stored procedure conditionally, and finally returning those values to the user that you were originally returning.
0
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 2000 total points
ID: 39730135
Looks like I should have used := for the assignments.
http://dev.mysql.com/doc/refman/5.5/en/user-variables.html

CREATE PROCEDURE reportFreeCoolingTrackerCalls (
        IN fromDate varchar (50),
        IN toDate varchar (50),
        IN timeZone varchar (50))

    BEGIN
            DECLARE startDate varchar (50);
            DECLARE endDate varchar (50);
            DECLARE mylogID Int;
            DECLARE @ActivityID1 int;
            DECLARE @ActivityID2 int;

             SET startDate = FROM_UNIXTIME(fromDate/1000);
             SET endDate = FROM_UNIXTIME(toDate/1000);
            

SELECT @ActivityID1 := l1.activityId, @ActivityID2 := t2.activityId
  FROM logs l 
      INNER JOIN groups g ON g.groupId = l.groupId
      LEFT JOIN groups g1 ON g.parentId = g1.groupId
      LEFT JOIN groups g2 ON g1.parentId = g2.groupId
      LEFT JOIN groups g3 ON g2.parentId = g3.groupId                
      INNER JOIN activities a ON l.logId = a.logId 
      INNER JOIN log1644 l1 ON a.activityId = l1.activityId 
      INNER JOIN log1644 t2 ON t2.recordId = l1.recordid + 1
      INNER JOIN items i ON l.logId = i.logId AND i.name LIKE '%KW%'
      INNER JOIN users u ON l1.userId = u.userId AND i.name LIKE '%KW%'
     WHERE i.itemID = "31985"  AND  l1.activityId = 1257
      AND l1.started
        BETWEEN startDate
            AND endDate 
     ORDER BY l1.recordId,l1.started;

     IF (@ActivityID1 = @ActivityID2) 
      THEN CALL reportFreeCoolingTrackerError (
        fromDate,
        toDate,
        timeZone );
    ELSEIF  (@ActivityID1 != @ActivityID2)
       THEN CALL reportFreeCoolingTracker (
        fromDate,
        toDate,
        timeZone );
            END IF;

         
  Select @ActivityID1, @ActivityID2;
  
    END //

DELIMITER ;
                                            

Open in new window

0
 

Author Comment

by:portlight
ID: 39730391
When I execute this it actually pulls 3 distinct result sets.
I only need it to pull the result set for the query actually executed
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

927 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question