How do I loop through a record set in a stored procedure?

i came across a situation where i had to loop through a recordset, have to pick some column values and need to insert it to another table.

A sample snippet as below :

DECLARE MyCursor CURSOR READONLY FORWARD SELECT id FROM mainNav ORDER BY sort,id

OPEN MyCursor
READ NEXT FROM MyCursor INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT id,label,url,sort,mainnavid FROM SubNav

WHERE mainNavID=@id ORDER BY sort,id
READ NEXT FROM MyCursor INTO @id
END
CLOSE MyCursor
DEALLOCATE MyCursor

attached the file script that i have used for the project.

-- ========================
-- Inserting Notifications –
-- =========================

ALTER PROCEDURE [dbo].[NotificationJob] AS
declare @TransId Int
declare @ToUserId Int
declare @RequestId Int
declare @FileId Int
declare @NoOfDays int
declare @TransType nvarchar(50)
declare @TransTypeId int
declare @NotificationType nvarchar(50)
declare @Active Int
declare @Description nVarchar (max)
declare @CreatedBy nvarchar(50)
declare @RecordCount Int

set @CreatedBy='DB_JOB'

--opening a cursor to select records from the Transaction table

DECLARE CUR_Notification
CURSOR FOR
SELECT
FileTransaction.TransId,
FileTransaction.UserId,
FileTransaction.FlowID,
FileTransaction.FileId,
FLOOR(CONVERT(real, GETDATE()) - FileTransaction.TransDate),
FileTransactionType.Name,
FileTransactionType.TransTypeId
FROM
FileTransaction
INNER JOIN
FileTransactionType
ON
FileTransaction.TransTypeId = FileTransactionType.TransTypeId
where
FLOOR(CONVERT(real, GETDATE()) - FileTransaction.TransDate) > 12
AND
FileTransactionType.TransTypeId= dbo.GetTransStatusId('Issue')
Or
FileTransactionType.TransTypeId= dbo.GetTransStatusId('Forward')
Or
FileTransactionType.TransTypeId= dbo.GetTransStatusId('AcceptReturn')

-- Opening the Cursor

OPEN CUR_Notification
FETCH NEXT FROM CUR_Notification
INTO @TransId, @ToUserId, @RequestId,@FileId, @NoOfDays, @TransType, @TransTypeId

declare @NotificationDate real

set @NotificationDate=(select CONVERT(real, GetDate()))

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @RecordCount=COUNT(*) FROM FRSNotification
WHERE
FRSNotification.TransID=@TransId
AND
FRSNotification.ToUserID=@ToUserId
AND
FRSNotification.RequestID=@RequestId

-- checking whether is there are any records exists

if @RecordCount=0
exec FRSNotification_Insert @TransId, @ToUserId,
@RequestId, @FileId, @TransTypeId, 1,
@NotificationDate, null, @TransType,
@NotificationDate, @CreatedBy

FETCH NEXT FROM CUR_Notification
INTO @TransId, @ToUserId, @RequestId,@FileId,
@NoOfDays, @TransType, @TransTypeId

END

--cleaning up operations
CLOSE CUR_Notification
DEALLOCATE CUR_Notification