Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  Transact-SQL (2000)  Append/Update stored proc with loop

Author  Topic  jmj Starting Member 7 Posts Posted - 2002-07-15 : 14:28:09 I need help with a stored procedure. I'm also not sure if it should be all in a sp or if a trigger should be called. Each quarter I need to check for new students in a 2nd database and append them to my database. I can do that. Then I need to append all the report card fields for that quarter and any past quarters. ie: so if it's quarter 3 I add the student and then I need to add report card skills for quarter1 and quarter2 with placeholders. Then append the 3rd quarter. So if johnny is new the report card ends up looking like this: Reading ... ... A Writing ... ... A Math ... ... B I need for my stored procedure to look at the quarter and loop through adding report card skills for the past quarters. I have a table that holds the information about the beginning/end dates of the quarters- it looks like this: tbl_Quarters Fields: BeginningQ, EndingQ (both datefields to show the beginning and end of the quarter) Quarter (int 1-4) I'm not sure how to make this happen.. I've attached a hard coded stored procedure of an example of the information that I use to add comments. I obviously don't want to be going through and doing this manually each quarter. Any ideas/help? Thanks in advance, J CREATE PROC AppendCommentsForNewStudents AS INSERT INTO tbl_Comments (StudentID, SCHOOLNUM, ClassID, GRADE) SELECT SI.StudentID, SI.SCHOOLNUM, CI.ClassID, SI.GRADE FROM tbl_StudentInformation SI INNER JOIN tbl_ClassInfo CI ON SI.StudentID=CI.StudentID WHERE (SI.DATEENTERED >='1/14/02') UPDATE tbl_Comments SET Quarter='4' WHERE Quarter IS Null