How to continue cursor loop even error occured in the loop

I want to insert rows of one table into another. For that I have the below procedure.

ALTER PROCEDURE LOADDATA_a
AS 
BEGIN

TRUNCATE TABLE STIDENT_A
DECLARE @SID INT
DECLARE @SNAME VARCHAR(50)
DECLARE @SUB VARCHAR(50)
DECLARE @MARKS INT

DECLARE LOAD_DATA CURSOR FAST_FORWARD FOR  
SELECT SID,SNAME,SUB,MARKS 
FROM student 

OPEN LOAD_DATA   
FETCH NEXT FROM LOAD_DATA INTO @SID,@SNAME,@SUB,@MARKS

WHILE @@FETCH_STATUS = 0   
BEGIN
    BEGIN TRANSACTION

    INSERT INTO STIDENT_A(SID,SNAME,SUB,MARKS) VALUES (@SID,@SNAME,@SUB,@MARKS)

    IF @@ERROR != 0
    BEGIN
        ROLLBACK TRANSACTION
        RETURN
    END
    ELSE
    BEGIN
        COMMIT TRANSACTION
    END
    FETCH NEXT FROM LOAD_DATA INTO @SID,@SNAME,@SUB,@MARKS
END

CLOSE LOAD_DATA   
DEALLOCATE LOAD_DATA

END

Whenever a row fails to insert, the cursor fails and stops there, but I need it to continue. How to do that?

Apparently, you’ve got a RETURN statement immediately after ROLLBACK TRANSACTION. Have you tried just removing it?

Although you could also rewrite the body using TRY/CATCH instead, like this:

...
WHILE @@FETCH_STATUS = 0   
BEGIN
    BEGIN TRY
        INSERT INTO STIDENT_A(SID,SNAME,SUB,MARKS)
        VALUES (@SID,@SNAME,@SUB,@MARKS);
    END TRY
    BEGIN CATCH
      -- this section must have some statement,
      -- so, why not log the erroneous data to the screen at least?
      PRINT @SID;
      PRINT @SNAME;
      PRINT @SUB;
      PRINT @MARKS;
      PRINT '';  -- an empty line as a delimiter
      -- or, perhaps, into a table?
      --INSERT INTO SomeFailLog (SID,SNAME,SUB,MARKS)
      --VALUES (@SID,@SNAME,@SUB,@MARKS);
    END CATCH;
    FETCH NEXT FROM LOAD_DATA INTO @SID,@SNAME,@SUB,@MARKS;
END;
...

But if you know what specifically may cause the inserts to fail, it might be even better to come up with a single statement that would produce only valid data to insert.

For instance, if the issue is that some SIDs in student already exist in STIDENT_A and you need to omit them, you could simply try the following instead of your procedure:

INSERT INTO STIDENT_A (SID, SNAME, SUB, MARKS)
SELECT s.SID, s.SNAME, s.SUB, s.MARKS
FROM student AS s
LEFT JOIN STIDENT_A AS a ON s.SID = a.SID
WHERE a.SID IS NULL
;

If you specify what exactly may be the issue while transferring your data, we might be able to help you with finding the most efficient solution specifically for that.

UPDATE addressing the comments

If the issue is that the maximum length of SNAME in STIDENT_A is less than that of the same name column in student and some values may not fit, you could simply use a filter (a WHERE clause) to limit the inserted rows to those where the actual length of SNAME does not exceed a certain value:

INSERT INTO STIDENT_A (SID, SNAME, SUB, MARKS)
SELECT SID, SNAME, SUB, MARKS
WHERE LEN(SNAME) <= @maxlength
;

You could query the metadata to determine the maximum length of the required column. There are various ways, one is to use the sys.columns system catalog:

DECLARE @maxlength int;
SELECT @maxlength = max_length
FROM sys.columns
WHERE object_id = OBJECT_ID('STIDENT_A')
  AND name = 'SNAME'
;

To determine which rows could not be inserted:

INSERT INTO STIDENT_A (SID, SNAME, SUB, MARKS)
SELECT SID, SNAME, SUB, MARKS
WHERE LEN(SNAME) > @maxlength
;

Try This:-

DECLARE @intFlag INT
 SET @intFlag = 0
WHILE (@intFlag <=5)
BEGIN
    begin Try
        if @intFlag = 3
        begin
            SET @intFlag = @intFlag + 'A'
        End
    Else
        begin
            PRINT @intFlag
        end
    End Try

    Begin Catch
        --print 'Error'
        -- continue
    End catch
SET @intFlag = @intFlag + 1
END
GO