Wednesday, December 26, 2012

Selecting Record to using cursor in stored procedure

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

No comments:

Post a Comment