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, ______________________________________________________________________ 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=coldfusiona 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
