I keep saying this, there is an excellent book which covers all this in detail and more, http://www.amazon.com/exec/obidos/ASIN/1893115836/qid=1015531831/s=2-2/re= sr_2_2/103-3058344-6479802
my head is full of coffee, I'm buzzin, sure I'd love to post the code but I'd have to work it out first,and my head is buzzin, Hal helms developed a good stored proc tool which writes out stored procs for "time is an issue developer", I emailed him off list, he sent it to me and I installed it in studio, then I opened it and it said if you don't have a thuru knowledge of SP's then your cheating yourself, so I closed it down, and got the book, I'm no whizz, but I've got a strong grasp of the basics, sorry to be of absolutely no help, grow a conscience and redirect you to a book, J -----Original Message----- From: Joshua Tipton [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 07, 2002 8:00 PM To: CF-Talk Subject: Re: OT:Stored Procedure 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, > ______________________________________________________________________ Why Share? Dedicated Win 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=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
