Following is an example using cursor to fetch record from table and performing action on each row using while loop, also used try catch in this stored procedure
ALTER PROCEDURE [dbo].[Test_Procedure_By_Khalid]
(
-- Add the
parameters for the stored procedure here
@TableName
nvarchar(255),
@AreaName varchar(50),
@SysDate DateTime,
@ReturnCode INT OUTPUT
)
AS
DECLARE
@GraphID NVARCHAR(50)
DECLARE
@PERIOD INT
DECLARE
@MODELKEY NVARCHAR(50)
DECLARE
@ORIGNALDATE NVARCHAR(50)
DECLARE @DATE
INT
DECLARE
@QTY INT
DECLARE
@TOTALQTY INT
DECLARE
@MODELKEYTYPE INT
DECLARE
@SCREENID NVARCHAR(50)
DECLARE
@RetailModelClassificationName NVARCHAR(50)
DECLARE
@SELECTEDAREA varchar(50)
DECLARE @INF_Cursor
INT
DECLARE
@THEMONTH NVARCHAR(6)
DECLARE
@LASTMONTH NVARCHAR(6)
DECLARE @LASTYEARMONTH NVARCHAR(6)
--Cursor Definition
DECLARE @Cur_TableParm CURSOR
BEGIN TRY
----------------------------
-- Start
Transaction --
----------------------------
BEGIN TRANSACTION
-- Set Cursor to select data from Table CPT_TableParm
SET
@Cur_TableParm = CURSOR
FAST_FORWARD FOR
SELECT GraphID,ModelKey,AreaName,ModelClassificationName,ScreenID FROM
CPT_TableParm
WHERE
TableName = @TableName
AND
ActFcType = 1
-- Executing
Cursor to select data from Table CPT_TableParm
OPEN @Cur_TableParm
FETCH NEXT FROM @Cur_TableParm
INTO
@GraphID, @MODELKEY,@SELECTEDAREA,
@RetailModelClassificationName,@SCREENID
-- Looping
through cursor result
WHILE @@FETCH_STATUS
= 0
BEGIN
Print
@SELECTEDAREA+','+cast(@PERIOD as varchar)+'~'+@MODELKEY+'~'+@ORIGNALDATE+'~'+cast(@DATE as varchar)+'~'+cast(@QTY as varchar)+'~'+
cast(@TOTALQTY as varchar)+'~'+@GraphID+'~''3''~'+@SCREENID
SET @ReturnCode = 2
FETCH NEXT FROM
@Cur_TableParm INTO
@GraphID, @MODELKEY,@SELECTEDAREA,
@RetailModelClassificationName,@SCREENID
END
-- Closing
@Cur_TableParm to select data from CPT_TableParm
CLOSE @Cur_TableParm
DEALLOCATE @Cur_TableParm
END TRY
BEGIN CATCH
-- Rollback Transaction --
ROLLBACK TRANSACTION
-- Reset Cursor
State
BEGIN
SET
@INF_Cursor = CURSOR_STATUS('global','@Cur_TableParm')
IF
@INF_Cursor = -1
BEGIN
DEALLOCATE @Cur_TableParm -- Delete Cursor
END
ELSE
IF @INF_Cursor <>
-3
BEGIN
CLOSE @Cur_TableParm -- Close Cursor
DEALLOCATE @Cur_TableParm -- Delete Cursor
END
PRINT
ERROR_MESSAGE()
END
RETURN 999
END CATCH
