Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 9 of 9 total |
Calling a Stored Proc from another Stored Proc. |
Wed, Nov 11 2015 2:02 PM | Permanent Link |
Gregory T Swain Zucchetti North America LLC | 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. |
Thu, Nov 12 2015 5:31 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Thu, Nov 12 2015 8:56 AM | Permanent Link |
Gregory T Swain Zucchetti North America LLC | 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 Attachments: UnifiedDepartureReportex.SQL |
Thu, Nov 12 2015 9:37 AM | Permanent Link |
Uli Becker | 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 |
Thu, Nov 12 2015 9:57 AM | Permanent Link |
Gregory T Swain Zucchetti North America LLC | Good Idea, I will utilize a temp table to share data between the two procedures.
Thanks |
Thu, Nov 12 2015 10:36 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Thu, Nov 12 2015 11:46 AM | Permanent Link |
Gregory T Swain Zucchetti North America LLC | 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. |
Thu, Nov 12 2015 12:33 PM | Permanent Link |
Gregory T Swain Zucchetti North America LLC | 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.
|
Fri, Nov 13 2015 4:21 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |