SQL cursor example

-- [insert] 'ex'
create procedure[insert]

@LoginId varchar(50)

as

begin

-- DECLARE @iErrorDetailId INT                        

-- BEGIN TRANSACTION                                

-- BEGIN TRY

declare @FileId int = null

declare @Sender varchar(200) = null

declare @Recepient varchar(200) = null

declare @RecStatus char(1)

declare @IsProcessed char(1)

declare @ProcessedOn datetime = null

declare @EstProcessedOn datetime = null

declare @ProcessType char(1) = null

declare @TemplateId int = null

declare @StartDate datetime = null

declare @EndDate datetime = null

declare @TrackingNum int = null

declare @StageCode int

--print 'Cursor start'

declare @TrackLogCursor CURSOR

SET @TrackLogCursor = CURSOR

FOR

 select 1 as [file_id],'Shekhar' as Sender,'Satish' as Recepient,'P' as rec_status,'N' as is_processed,tl.tr_end_dt as processed_on,
 tl.tr_end_dt as est_processed_on,'S' as process_type,1 as template_id,tl.tr_start_dt as tr_start_dt,tl.tr_end_dt as tr_end_dt,
 tl.tracking_num as tracking_num,tl.stage_code as stage_code
 from tralog tl left outer join
  aleque aq on aq.tracking_num <> tl.tracking_num and aq.stage_code <> tl.stage_code
 where tl.tr_date is null and tl.tr_end_dt = CAST(CURRENT_TIMESTAMP AS DATE)
 order by tl.tr_end_dt

--print 'Open Cursor'

OPEN @TrackLogCursor

FETCH NEXT FROM @TrackLogCursor INTO @FileId,@Sender,@Recepient,@RecStatus,@IsProcessed,@ProcessedOn,@EstProcessedOn,@ProcessType,@TemplateId,
@StartDate,@EndDate,@TrackingNum,@StageCode

WHILE @@FETCH_STATUS = 0

BEGIN

begin

insert into aleque ([file_id],Sender,Recepient,rec_status,is_processed,processed_on,est_processed_on,process_type,template_id,
tr_start_dt,tr_end_dt,tracking_num,stage_code,updated_by,updated_on)
values(@FileId,@Sender,@Recepient,@RecStatus,@IsProcessed,@ProcessedOn,@EstProcessedOn,@ProcessType,@TemplateId,@StartDate,@EndDate,
  @TrackingNum,@StageCode,@LoginId,GETDATE())

end  

FETCH NEXT FROM @TrackLogCursor INTO @FileId,@Sender,@Recepient,@RecStatus,@IsProcessed,@ProcessedOn,@EstProcessedOn,@ProcessType,@TemplateId,
@StartDate,@EndDate,@TrackingNum,@StageCode

END

CLOSE @TrackLogCursor

DEALLOCATE @TrackLogCursor

--start cursor for insert update record in table

--COMMIT TRANSACTION      

--END TRY      



--BEGIN CATCH      

--ROLLBACK TRANSACTION      

--EXEC [uspLog] @psSchemaName = NULL, @piErrorDetailId=@iErrorDetailId OUTPUT;      

--RAISERROR('Error: [CRM].[syncuserinfo]',-1,-1)      

--END CATCH
end