2 points. First if SP-1 calls SP-2 SP-2 can see all temporary tables
created by SP-1 BUT NOT VISA-VERSA. So if you are going to create a
result
set that you later want to access in the calling SP you have to "trick"
the
SP.
The way we've done this is to create an empty table in the calling SP
(SP-1)
with the proper structure. Then call the SP (SP-2) passing in the
table
name. We code SP-2 to run in 2 modes. If a table name is passed then
it
creates the final result set it does it with a "INSERT INTO <tablename>
SELECT ...." command. Otherwise it just does a "SELECT ...." command.
This
way we can get the results passed back to the calling SP for it's use.
Secondly, you can probably do the query you've given with 1 select
statement. I use MS SQL Server and I've done queries like this several
times. I've never worked with Oracle so all bets are off there.
SELECT GRADES.*,
STDNT.*
FROM GRADES
INNER JOIN (SELECT STUDENTS.*,
schol.*
FROM STUDENTS
INNER JOIN (SELECT SchoolID, blah,blah
FROM school
WHERE SchoolID =
#SchoolID#) schol
ON STUDENTS.SchoolID = SCHOL.SchoolID
WHERE gradeID=#gradeID#) STDNT
ON GRADES.StudentID = STDNT.StudentID
Hope this helped.
______________________________________________________
Bill Grover
Supervisor MIS Phone: 301.424.3300 x3324
EU Services, Inc. FAX: 301.424.3696
649 North Horners Lane E-Mail: [EMAIL PROTECTED]
Rockville, MD 20850-1299 WWW: http://www.euservices.com
______________________________________________________
> -----Original Message-----
> From: Janine Jakim [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, March 07, 2002 2:49 PM
> To: CF-Talk
> Subject: OT:Stored Procedure
>
>
> I have a report that has 6 queries to gather all the info
> from the related
> tables. I am in the process of turning these <cfquery>s into stored
> procedures (SQL2000).
> In CF I have things like
> <CFQUERY name="Q1"> SELECT blah,blah
> FROM SCHOOLS
> WHERE SchoolID=#SchoolID#
>
> <CFQUERY name="Q2">
> SELECT *
> FROM STUDENTS
> WHERE SchoolID=#SchoolID#
> AND gradeID=#gradeID#
>
> <CFSET StudentID=Q2.StudentID>
>
> <CFQUERY NAME="Q3">
> SELECT *
> FROM GRADES
> WHERE StudentID=#StudentID#
>
> So query1 gets the schoolid- query2 gets all the students with that
> schoolid/gradeID and then query3 is getting the grades for
> those students.
>
> I'm wondering how to do this in stored procedures. I've
> always learned to
> keep the sp small so they can be reused. I was also told
> that one stored
> procedure can call another- so I guess I was wondering if one stored
> procedure can pass a parameter to the next. (ie: so it knows
> what students
> to pull grades for....)
> If this is possible where or how would I put it in the stored
> procedure?
> ie:
> one sp would be:
> CREATE PROCEDURE GetSchools
> @SchoolID int,
> @GradeLevel int
> AS
> SELECT *
> FROM Students
> WHERE SchoolID=@SchoolID
> AND GradeLevel=@GradeLevel
> GO
> So how would I filter the StudentId from this stored
> procedure to put in the
> next stored procedure:
>
> CREATE PROCEDURE GetReportCardMarks
> @StudentID int
> AS
> SELECT Skill, StudentID, Grade, SchoolID
> FROM Grades
> WHERE StudentID=@StudentID
> GO
>
> I hope I explained this sufficiently.
> Thanks in advance,
>
______________________________________________________________________
Get Your Own Dedicated Windows 2000 Server
PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER
Instant Activation � $99/Month � Free Setup
http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
