Seems like a simple thing, but I'm struggling here a bit. Hoping someone could provide some insight. I have a SP that returns a result set. I need to insert this result set into a temp table within another stored proc.


...
     EXECUTE IMMEDIATE
        'INSERT INTO
           tmpReportResults(
            ReportName,
            ResNumber,
            GuestName,
            DateOfFinancial,
            Cancelled,
            Arrival,
            Departure,
            Amount)
         spDepositsMadeInMonthCancelledInMonth ? ? ? ?
        ' USING ReportDate, RoomsFilter, AllRooms, FullReport;
....
I'm able to do this same thing using a Select statement in lou of the spDeposits... but not able to do this using a sp. Iv'e tried Exec spDepo.... and Call spDep.... with no such luck.

Any and all help will be greatly appreciated.

Gregory


I have tried and failed to get my head round what it is you're doing with the information you've supplied and I've failed. My immediate guess is that what you're trying to do isn't possible but I just don't have enough to go on to say for sure. Can you post some more, including the full code you're using please.


Roy Lambert

Essentially I am taking 12+ reports queries and integrating them into a single unified report. This will allow the ability to run a Summary Report with all of the values sum(total), or to do a line by line for financial reports. It's a fairly simple concept in the report. This is a working proof of concept right now, once all of the reports are integrated I will make it a stored procedure (the attached file) that is able to be called quickly from the report generation. For now you should be able to figure out what will be input, etc.

The issue I am having is all but 1 report query is a simple select with a few params to pass in. No big deal with cursors. The one-off (code attached in msg) is a stored procedure that needs to be called.

What I need to do is take the results from the stored procedure and dump them into the tmpResults table within the UnifiedDepartureReport.

Other flavors of SQL allow you to do an ONROWSET, or something like:

CREATE TABLE #tmptbl
(
  COL1 INT,
  COL2 INT
)

INSERT INTO #tmptbl
Exec SpGetRecords 'Params'





----Stored Proc to call

ALTER PROCEDURE "spDepositsMadeInMonthCancelledInMonth" (IN "ReportDate" DATE, IN "RoomsFilter" VARCHAR COLLATE AFK, IN "AllRooms" BOOLEAN, IN "FullReport" BOOLEAN)
BEGIN
DECLARE REPORT_CURSOR CURSOR WITH RETURN FOR REPORT_STMT;
  DECLARE ResNumberCursor CURSOR FOR ResNumberStmt;
  DECLARE ResNumber INT;            
  DECLARE StartDate DATE;
  DECLARE EndDate DATE;

  
DECLARE InsertString VARCHAR DEFAULT
         'INSERT INTO
           tmpResults(
            ReportName,
            ResNumber,
            GuestName,
            DateOfFinancial,
            Cancelled,
            Arrival,
            Departure,
            Amount)';
     BEGIN
  SET StartDate = fnFirstOfTheMonth(ReportDate);
  SET EndDate = fnFirstOfNextMonth(ReportDate);
--Clean tmpTables
     BEGIN
        EXECUTE IMMEDIATE 'DROP TABLE tmpResNumber';
        EXECUTE IMMEDIATE 'DROP TABLE tmpResults';

     EXCEPTION
        SET LOG MESSAGE TO ERRORMSG();
     END;

        EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLE
                             tmpResNumber(
                                ResNumber Int
                          )';
     EXCEPTION
        SET LOG MESSAGE TO ERRORMSG();
     END;

     BEGIN
        EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLE
                             tmpResults(
                                ReportName varchar(255),
                                ResNumber Int,
                                GuestName varchar(255),
                                DateOfFinancial TIMESTAMP,
                                Arrival Date,
                                Cancelled Date,
                                Departure Date,
                                Amount Decimal(19,2)
                          )';
     EXCEPTION
        SET LOG MESSAGE TO ERRORMSG();
     END;


   ---get deposits this month
   EXECUTE IMMEDIATE
        'INSERT INTO tmpResNumber(ResNumber)
        (SELECT
          f.ResNumber
       FROM
          tblguestdates gd
       INNER JOIN
          tblnames n on n.ResNumber = gd.ResNumber
       INNER JOIN
           tblfinancial_l f on f.ResNumber = gd.ResNumber
      
       WHERE   
           n.NameType=''GST'' AND
           f.TransType in (100,101,102,122,130) AND
           f.DateOfFinancial >= ? and f.DateOfFinancial < ? AND
           gd.Cancelled >= ? and  gd.Cancelled < ?  AND
       CASE WHEN '+ CAST(AllRooms as VARCHAR(6))   +' THEN 1=1 ELSE (SELECT MAX(RoomId) from tblGuestRoomRates where ResNumber=gd.ResNumber) IN (?) END
      INTERSECT
      SELECT
         f.ResNumber
       FROM
          tblguestdates gd
       INNER JOIN
          tblnames n on n.ResNumber = gd.ResNumber
       INNER JOIN
           tblfinancial_l f on f.ResNumber = gd.ResNumber
       WHERE   
           n.NameType=''GST'' AND
           f.TransType in (120, 121, 122, 135, 145, 155, 165, 175, 185, 210, 212, 250) AND
           f.DateOfFinancial >= ? and f.DateOfFinancial < ? AND
           gd.Cancelled >= ? and  gd.Cancelled < ? AND
       CASE WHEN '+ CAST(AllRooms as VARCHAR(6))   +' THEN 1=1 ELSE (SELECT MAX(RoomId) from tblGuestRoomRates where ResNumber=gd.ResNumber) IN (?) END
        )

        
        ' USING StartDate, EndDate, StartDate, EndDate, RoomsFilter, StartDate, EndDate, StartDate, EndDate, RoomsFilter;

  
  PREPARE ResNumberStmt FROM 'SELECT * FROM tmpResNumber';

  OPEN ResNumberCursor;


        EXECUTE IMMEDIATE
        InsertString + '
        SELECT
          ''*Deposits made this Month'',
          gd.ResNumber,
          GuestName(n.FirstName,n.LastName, n.Company) as "Name",
          f.DateOfFinancial,
          gd.Cancelled,
          gd.Arrival,
          gd.Departure,
          sum(f.Amount) as "Amount"
  
       FROM
          tblFinancial_l f
       left JOIN
          tblguestdates gd on gd.ResNumber=f.ResNumber
       left JOIN
          tblnames n on n.ResNumber = f.ResNumber
       WHERE
           f.ResNumber in (SELECT ResNumber from tmpResNumber) AND
           (f.TransType in (120, 121, 122, 135, 145, 155, 165, 175, 185, 210, 212, 250) OR
            f.TransType in (100,101,102,122,130)) AND
            f.DateOfFinancial >= ? and   f.DateOfFinancial < ?
        GROUP BY
          CASE WHEN '+ Cast(FullReport as Varchar(9)) +'  THEN DateOfFinancial  ELSE current_date()  END

        ' Using  StartDate, EndDate;

     PREPARE REPORT_STMT FROM 'Select * From tmpResults        
       ORDER BY
          GuestName';
     OPEN REPORT_CURSOR;
END

Greg,

> What I need to do is take the results from the stored procedure and dump them into the tmpResults table within the UnifiedDepartureReport.

I don't think it's possible what you try:

http://tinyurl.com/ohajchm

One possibility would be to add some code at the end of your sp in order
to store the records in a temporary table. Then you can easily do what
you want.

Uli

Good Idea, I will utilize a temp table to share data between the two procedures.

Thanks

Gregory

>Good Idea, I will utilize a temp table to share data between the two procedures.

I was wondering about that - this is the main thing I couldn't get my head round - if you wanted it in a temporary table why not put it there in the first procedure rather than trying to do so in the second.

Roy

Worked great. I actually was already dumping into a temp table within the report stored procedure, so it was a few lines and done.

Thanks again.

I actually already did have it in a temp table, I just was thinking about it in a different way. Rather than thinking about using the temp table to query I was expecting to query the result set of the SP, which really was the temp table anyways.

Gregory

>I actually already did have it in a temp table, I just was thinking about it in a different way. Rather than thinking about using the temp table to query I was expecting to query the result set of the SP, which really was the temp table anyways.



Roy