table variable not reseting
-
I've defined a group of students (eg. varsity soccer players) whom I want to query class grades for. I have a student groupId which equals 5 for the varsity soccer players. I've setup a table variable which houses all the students who are members of groupId 5 so that I can iterate through the table. Within each iteration of the students table variable I declare a classes table variable to house the individual student classes. I can then call a user defined function which returns a students score for a given class. After I harvest studentId, classId and student score for classId I write that to yet another table variable for further processing. Here is my code my results and my problem:
Code:
ALTER PROCEDURE [dbo].[repStudentGroupHits]
-- Add the parameters for the stored procedure here
@groupId int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @groupTermId int
DECLARE @criteriaOperator int
DECLARE @criteriaValue float
DECLARE @groupData table (rownum int IDENTITY(1, 1) Primary key NOT NULL, tempRecId int, studentId int, firstName nvarchar(50), lastName nvarchar(50), classId int, scoreValue float)
DECLARE @studentsInGroup table (rownum int IDENTITY (1, 1) Primary key NOT NULL, studentId int, firstName nvarchar(50), lastName nvarchar(50))
DECLARE @currentStudent int
DECLARE @currentFirstName nvarchar(50)
DECLARE @currentLastName nvarchar(50)
DECLARE @studentRowCnt int
DECLARE @studentMaxRows int
DECLARE @classesPerStudent table (rownum int IDENTITY (1, 1) Primary key NOT NULL, classId int, categoryHeaderId int)
DECLARE @currentClass int
DECLARE @currentClassCategoryHeader int
DECLARE @classRowCnt int
DECLARE @classMaxRows int
SELECT @groupTermId = termId, @criteriaOperator = gradeCriteria, @criteriaValue = gradeThreshold
FROM tblStudentGroups
WHERE (groupId = @groupId)
-- Insert qualifying students into student table variable for processing
INSERT INTO @studentsInGroup SELECT tblStudentGroupMembership.studentId, tblStudents.firstName, tblStudents.lastName
FROM tblStudentGroupMembership INNER JOIN tblStudents ON tblStudentGroupMembership.studentId = tblStudents.studentId
WHERE (tblStudentGroupMembership.groupId = @groupId)
ORDER BY tblStudents.lastName, tblStudents.firstName
SELECT @studentRowCnt = 1
SELECT @studentMaxRows = COUNT(*) FROM @studentsInGroup
WHILE @studentRowCnt <= @studentMaxRows
BEGIN
SELECT @currentStudent = studentId, @currentFirstName = firstName, @currentLastName = lastName FROM @studentsInGroup WHERE rownum = @studentRowCnt
--here we have the current student sorted by last name, now we need to obtain every classId that this
--student is enrolled in
INSERT INTO @classesPerStudent SELECT tblClasses.classId, tblClasses.categoryHeaderId
FROM tblClasses INNER JOIN tblEnrollment ON tblClasses.classId = tblEnrollment.classId INNER JOIN tblStudents ON tblEnrollment.studentId = tblStudents.studentId
WHERE (tblStudents.studentId = @currentStudent) AND (tblClasses.termId = @groupTermId)
SELECT @classRowCnt = 1
SELECT @classMaxRows = COUNT(*) FROM @classesPerStudent
WHILE @classRowCnt <= @classMaxRows
BEGIN
SELECT @currentClass = classId, @currentClassCategoryHeader = categoryHeaderId FROM @classesPerStudent WHERE rownum = @classRowCnt
--at this point we have the current student sorted by last name and the current class, now we need to
--run the appropriate score calculating function per class/student and write the values to @groupData
If (@currentClassCategoryHeader = 1)
BEGIN
INSERT INTO @groupData (tempRecId, studentId, firstName, lastName, classId, scoreValue) VALUES (@currentClassCategoryHeader, @currentStudent, @currentFirstName, @currentLastName, @currentClass, dbo.sudfGetSingleStudentSaFinal(@currentClass,@currentStudent))
END
ELSE
BEGIN
INSERT INTO @groupData (tempRecId, studentId, firstName, lastName, classId, scoreValue) VALUES (@currentClassCategoryHeader, @currentStudent, @currentFirstName, @currentLastName, @currentClass, dbo.sudfGetSingleStudentCwFinal(@currentClass,@currentStudent))
END
Select @classRowCnt = @classRowCnt + 1
END
Select @studentRowCnt = @studentRowCnt + 1
--every student iteration should start with a blank class table
DELETE FROM @classesPerStudent
END
--This select should be sorted by student last name by default
--SELECT * FROM @groupData
SELECT tempRecId, studentId, classId, scoreValue FROM @groupData
END
Results:
Problem:
As seen the first student processes just fine however the classId isn't reseting on any further student iterations. All subsequent student classIds are equal to the the last classId of the first student (133).
Can anyone spot my problem?
Thanks for considering.
-d
-
Delete doesn't reset identity values. If you need to do that, you need to use temp tables and use truncate instead of delete.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
Thank you for this idea, however I'm not convinced that the identity column values are the problem based on these two thoughts:
1) I can't conceive of the following query returning 133 if the where clause filtered all records which is what I think you are suggesting:
SELECT @currentClass = classId, @currentClassCategoryHeader = categoryHeaderId FROM @classesPerStudent WHERE rownum = @classRowCnt I would expect a null value.
2) The above query is working correctly as far as record counts go. If you look at studentId 58, I have confirmed that he is only enrolled in 3 classes.
If my logic above is inacurate please help me understand where.
Thanks again.
-
Per your first post, the problem is the ID not resetting. Maybe I'm misreading this:
As seen the first student processes just fine however the classId isn't reseting on any further student iterations. All subsequent student classIds are equal to the the last classId of the first student (133).
Since I don't have the tables or data to test your code and see what it's doing, it's hard for me to spot the exact problem if it's not just an ID not resetting. Can you provide table definitions and an insert statement for some sample data?
Edit: Fixed typo.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
You're exactly correct witht the identity values not resetting. I've modified the nested loop using one of Dinakar Nethi's techniquies
Here is the final code that works if anyone is interested:
ALTER PROCEDURE [dbo].[repStudentGroupHits]
-- Add the parameters for the stored procedure here
@groupId int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @groupTermId int
DECLARE @criteriaOperator int
DECLARE @criteriaValue float
DECLARE @groupData table (rownum int IDENTITY(1, 1) Primary key NOT NULL, studentId int, firstName nvarchar(50), lastName nvarchar(50), classId int, scoreValue float)
DECLARE @studentsInGroup table (rownum int IDENTITY (1, 1) Primary key NOT NULL, studentId int, firstName nvarchar(50), lastName nvarchar(50))
DECLARE @currentStudent int
DECLARE @currentFirstName nvarchar(50)
DECLARE @currentLastName nvarchar(50)
DECLARE @studentRowCnt int
DECLARE @studentMaxRows int
DECLARE @classesPerStudent table (processed tinyint, classId int, categoryHeaderId int)
DECLARE @currentClass int
DECLARE @currentClassCategoryHeader int
SELECT @groupTermId = termId, @criteriaOperator = gradeCriteria, @criteriaValue = gradeThreshold
FROM tblStudentGroups
WHERE (groupId = @groupId)
-- Insert qualifying students into student table variable for processing
INSERT INTO @studentsInGroup SELECT tblStudentGroupMembership.studentId, tblStudents.firstName, tblStudents.lastName
FROM tblStudentGroupMembership INNER JOIN tblStudents ON tblStudentGroupMembership.studentId = tblStudents.studentId
WHERE (tblStudentGroupMembership.groupId = @groupId)
ORDER BY tblStudents.lastName, tblStudents.firstName
SELECT @studentRowCnt = 1
SELECT @studentMaxRows = COUNT(*) FROM @studentsInGroup
WHILE @studentRowCnt <= @studentMaxRows
BEGIN
SELECT @currentStudent = studentId, @currentFirstName = firstName, @currentLastName = lastName FROM @studentsInGroup WHERE rownum = @studentRowCnt
--here we have the current student sorted by last name, now we need to check every class that this
--student is enrolled in
INSERT INTO @classesPerStudent SELECT 0, tblClasses.classId, tblClasses.categoryHeaderId
FROM tblClasses INNER JOIN tblEnrollment ON tblClasses.classId = tblEnrollment.classId INNER JOIN tblStudents ON tblEnrollment.studentId = tblStudents.studentId
WHERE (tblStudents.studentId = @currentStudent) AND (tblClasses.termId = @groupTermId)
WHILE EXISTS (SELECT * FROM @classesPerStudent WHERE processed = 0)
BEGIN
SELECT @currentClass = MIN(classId) FROM @classesPerStudent WHERE processed = 0
SELECT @currentClassCategoryHeader = categoryHeaderId FROM @classesPerStudent WHERE classId = @currentClass
--at this point we have the current student sorted by last name and the current class, now we need to
--run the appropriate score calculating function per class/student and write the values to @groupData
If (@currentClassCategoryHeader = 1)
BEGIN
INSERT INTO @groupData (studentId, firstName, lastName, classId, scoreValue) VALUES (@currentStudent, @currentFirstName, @currentLastName, @currentClass, dbo.sudfGetSingleStudentSaFinal(@currentClass,@currentStudent))
END
ELSE
BEGIN
INSERT INTO @groupData (studentId, firstName, lastName, classId, scoreValue) VALUES (@currentStudent, @currentFirstName, @currentLastName, @currentClass, dbo.sudfGetSingleStudentCwFinal(@currentClass,@currentStudent))
END
UPDATE @classesPerStudent SET processed = 1 WHERE classId = @currentClass
END
Select @studentRowCnt = @studentRowCnt + 1
--every student iteration should start with a blank class table
DELETE FROM @classesPerStudent
END
--This select should be sorted by student last name by default
SELECT * FROM @groupData
END
Thank you for your time and consideration.
-
Maybe I'm not looking at this problem hard enough, but I don't see the reason for a loop at all. This should be done using set based code.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
As soon as I read the code and saw you posted I knew what your post would be..
CEWII
-
lcsgeek (10/20/2009)
You're exactly correct witht the identity values not resetting. I've modified the nested loop using one of Dinakar Nethi's techniquiesHere is the final code that works if anyone is interested:
Thank you for posting your final solution but, I have to tell you, Dinakar Nethi's technique did nothing to help you. It's not a cursor that's so bad... it's the WHILE Loop that's the big problem.
At this point, I don't know if you are interested, but if you were to post the Table Creation statements and provide some test data in the format outlined in the first link in my signature line below, someone may take the time to help you convert this to some high performance set based code.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply