The FETCH statement only returns one row from the result set. If you want to return all rows, you need to put the FETCH statement in a loop. A simple way to stop the loop to check the system variable @@FETCH_STATUS, which returns a status code of the last FETCH statement:

@status = @@FETCH_STATUS;

-- Returns 0, the fetch was successful

-- Returns -1, the fetch failed or end of result set reached

-- Returns -2, the row fetched is missing

The tutorial exercise below shows how @@FETCH_STATUS is used to make WHILE loop over the result set in a cursor:

USE GlobalGuideLineDatabase;

GO