Solved

Calling one stored procedure from another

Posted on 2013-12-19
6
620 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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 500 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
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…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

617 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