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
