Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Calling one stored procedure from another

Posted on 2013-12-19
6
Medium Priority
?
644 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

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

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…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

578 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