Nested Queries to Stored Procedures

[scottetombleson]

Hello,

I am Upsizing an Access 2003 db to Sql 2000. I have one form that the dataset is based on a summary query which is in turn based on 9 queries that rely on a value in a control on the form.

I have the individual parameritized stored procedures working well, but I can't figure out how to bring it all together.

I've tried to execute the first SP from inside the summary SP thinking that I could then use the results of the first level in the summary SP, but all that returns is the results form the first SP.

I would be eternally grateful if some kind soul could help guide me here.

Thanks,

Scott

 

[JayKusch]

Sounds like you need to leverage either Temp Tables or Temp Variables w/in your Summary SP. Take a look at BOL (Books OnLine) for the definitions of both.

I figure you will probably have to use te TTs and execute a INSERT INTO ... EXEC spMySP1 to populate it. I do not belive you can use a TV w/ and EXEC in the same manner.

Hope this helps!



Thanks

J. Kusch

 

[scottetombleson]

Thanks Jay. So I'm on that path. Here is my Create table. I'm basing my synatx on other posts here. But I'e got something wrong.

CREATE PROCEDURE qryResDisplay @today datetime AS 

CREATE TABLE #qryResDisplay
(memtoday int,
restoday int,
bedid int,
firsttoday varchar(20),
lasttoday varchar(25))

INSERT INTO #qryResDisplay SELECT tblRES.FAMMEMID AS memtoday, tblRES.BEDID, tblRES.RESID AS restoday, [familyMembers].[FirstName] AS firsttoday, [FamilyMembers].[Lastname] AS lasttoday
FROM tblRES INNER JOIN FamilyMembers ON tblRES.FAMMEMID = FamilyMembers.FamMemID
WHERE ((@today>=[tblRES].[STARTDATE] And @today<=[tblRES].[ENDDATE]));
GO

Then here is my summary SP

CREATE PROCEDURE qrystOCCUPANCY2 @today datetime AS

exec qryResDisplay @today
exec [qryResDisplay1] @today
exec [qryResDisplay2] @today
exec [qryResDisplay3] @today
exec [qryResDisplay4] @today
exec [qryResDisplay5] @today
exec [qryResDisplay6] @today
exec [qryResDisplay7] @today
exec [qryResDisplaym1] @today
exec [qryResDisplaym2] @today

SELECT tblBUILDING.BUILDINGORDER, tblBEDS.BEDID, tblBEDS.ROOMID, tblBEDS.ROOM, tblBEDS.BED_NO, tblBEDS.BED_TYPE, #qryResDisplay.memtoday, #qryResDisplay.restoday, #qryResDisplay.firsttoday, #qryResDisplay.lasttoday, [#qryResDisplay1].mem1, 
[#qryResDisplay1].res1, [#qryResDisplay1].first1,  [#qryResDisplay1].last1, [#qryResDisplay2].mem2, [#qryResDisplay2].res2, [#qryResDisplay2].first2, [#qryResDisplay2].last2, [#qryResDisplay3].mem3, 
[#qryResDisplay3].first3, [#qryResDisplay3].last3, [#qryResDisplay3].res3, [#qryResDisplay4].mem4, [#qryResDisplay4].res4, [#qryResDisplay4].first4, [#qryResDisplay4].last4, [#qryResDisplaym1].memyesterday, 
[#qryResDisplaym1].firstyesterday, [#qryResDisplaym1].lastyesterday, [#qryResDisplaym1].resyesterday, [#qryResDisplaym2].memtwodaysago, [#qryResDisplaym2].restwodaysago, 
[#qryResDisplaym2].firsttwodaysago, [#qryResDisplaym2].lasttwodaysago, [#qryResDisplay5].mem5, [#qryResDisplay5].res5, [#qryResDisplay5].first5, [#qryResDisplay5].last5, [#qryResDisplay6].mem6, 
[#qryResDisplay6].res6, [#qryResDisplay6].first6, [#qryResDisplay6].last6, [#qryResDispaly7].mem7, [#qryResDispaly7].res7, [#qryResDispaly7].first7, [#qryResDispaly7].last7, tblROOMS.INACTIVE
FROM (((((((((((tblBEDS LEFT JOIN #qryResDisplay ON tblBEDS.BEDID = #qryResDisplay.BEDID) 
LEFT JOIN [#qryResDisplay1] ON tblBEDS.BEDID = [#qryResDisplay1].BEDID) 
LEFT JOIN [#qryResDisplay2] ON tblBEDS.BEDID = [#qryResDisplay2].BEDID) 
LEFT JOIN [#qryResDisplay3] ON tblBEDS.BEDID = [#qryResDisplay3].BEDID) 
LEFT JOIN [#qryResDisplay4] ON tblBEDS.BEDID = [#qryResDisplay4].BEDID) 
LEFT JOIN [#qryResDisplaym1] ON tblBEDS.BEDID = [#qryResDisplaym1].BEDID) 
LEFT JOIN [#qryResDisplaym2] ON tblBEDS.BEDID = [#qryResDisplaym2].BEDID) 
LEFT JOIN [#qryResDisplay5] ON tblBEDS.BEDID = [#qryResDisplay5].BEDID) 
LEFT JOIN [#qryResDisplay6] ON tblBEDS.BEDID = [#qryResDisplay6].BEDID) 
LEFT JOIN [#qryResDispaly7] ON tblBEDS.BEDID = [#qryResDispaly7].BEDID) 
LEFT JOIN tblROOMS ON tblBEDS.ROOMID = tblROOMS.ROOMID) 
LEFT JOIN tblBUILDING ON tblROOMS.BUILDING = tblBUILDING.BUILDINGCODE
ORDER BY tblBUILDING.BUILDINGORDER, tblROOMS.ROOM, tblBEDS.BED_NO;
GO

I get the error "ODBC--call failed" Invalid object name '#qryResDisplay1' over and over.

Can you help me with my syntax?

Thanks for your help.

Scott