back_and_forth.sql

DECLARE student_cursor SCROLL CURSOR FOR
SELECT [studentId], [name] FROM students
ORDER BY NEWID()


DECLARE @direction VARCHAR(10),
	@studentId INT,
	@name VARCHAR(50),
	@requestId INT,
	@requestCount INT
	
SET @direction = 'FORWARD'

OPEN student_cursor

FETCH NEXT FROM student_cursor
INTO @studentId, @name

IF @@FETCH_STATUS = 0
BEGIN

	--SELECT @requestCount = COUNT(*) FROM requests

	WHILE EXISTS( SELECT requestId FROM requests )
	BEGIN
	
		PRINT 'StudentId: ' + CAST( @studentId AS VARCHAR(10) ) + ' Name:' +  @name
		
		SET @requestId = NULL
		
		SELECT TOP 1 
			@requestId = requestId
		FROM
			requests
		WHERE
			studentId = @studentId
			
		IF ( @requestId IS NOT NULL )
		BEGIN		
			PRINT  'Deleting: ' + CAST( @requestId AS VARCHAR(10) )			
			DELETE FROM requests WHERE requestId = @requestId			
			SELECT @requestCount = COUNT(*) FROM requests		
		END
		
		
	
		IF (@direction = 'FORWARD')
		BEGIN
			FETCH NEXT FROM student_cursor
			INTO @studentId, @name
			IF @@FETCH_STATUS <> 0
			BEGIN
				FETCH PRIOR FROM student_cursor
				INTO @studentId, @name
				SET @direction = 'BACKWARD'
				PRINT 'Now moving BACKWARD'
			END
			CONTINUE
		END
		
		IF(@direction = 'BACKWARD')
		BEGIN
			FETCH PRIOR FROM student_cursor
			INTO @studentId, @name			
			IF @@FETCH_STATUS <> 0
			BEGIN
				FETCH NEXT FROM student_cursor
				INTO @studentId, @name
				SET @direction = 'FORWARD'
				PRINT 'Now moving FORWARD'

			END
			CONTINUE
		END
	
	END

END


CLOSE student_cursor
DEALLOCATE student_cursor