Problems in a "While" loop - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Problems in a "While" loop

Author

Topic

greenspacechunks
Starting Member

5 Posts

Posted - 2007-04-09 : 21:54:21

I have a simple while loop but am new to SQL. I know that it is a simple problem I am running into, but I do not know how to fix that simple problem. Whenever it performs the following statement, the SELECT statement limits the results to only the studentID of '1'. If I try to force it to go through, after it finishes the first student, it says that you cannot use a null value to insert into the new table. However, it is not a null value because there are more student's grades in the table to calculate. The SELECT statement is getting a view of only the first student. Hopefully I described my problem well enough; any help would be appreciated!

WHILE(@I <= @student_count)
BEGIN
	SET @I = @I + 1
	WHILE(@J <= @category_count)
	BEGIN
		SET @J = @J + 1
		SELECT @totalPoints = SUM(EarnedPoints)
		FROM       STUDENT INNER JOIN
                   GRADE ON STUDENT.StudentID = GRADE.StudentID
		WHERE GRADE.CategoryID = @J AND GRADE.StudentID = @I
		INSERT INTO [STUDENT_POINTS_CATEGORY]
			   ([StudentID]
				,[CategoryID]
				,Total_Earned_Points)
		  VALUES
			   (@I
				,@J
				,@totalPoints)
	END
END

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-04-09 : 22:09:09

I hope to God this wasn't some sort of really bad homework assignment. If so, and your instructor wants you to use loops like this, then I pity you. If not, then welcome to the world of set-based coding:


INSERT INTO [STUDENT_POINTS_CATEGORY]
	([StudentID],
	[CategoryID],
	Total_Earned_Points)
SELECT	Student.StudentID,
	GRADE.CategoryID,
	SUM(EarnedPoints)
FROM	STUDENT
	INNER JOIN GRADE ON STUDENT.StudentID = GRADE.StudentID

e4 d5 xd5 Nf6

greenspacechunks
Starting Member

5 Posts

Posted - 2007-04-09 : 22:19:47

Wow. I like that way a lot better. He didn't even point out that there are 'while' loops in SQL. I thought this was the best way, being a Java programmer, but I was wrong.

Worked perfectly! Lesson learned.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-04-10 : 09:44:53

The fact that your professor did not point out that there are WHILE loops in SQL is to his credit. Any sort of looping should be the last resort in tackling SQL coding problems.

e4 d5 xd5 Nf6

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources