Use Books online and read about declaring variables.
On Thu, 07 March 2002, Janine Jakim wrote: > > 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
