Getting @@ROWCOUNT when there is an insert query in SQL-SERVER?
@@ROWCOUNT is set after each statement so the value printed is for the SET statement instead of the INSERT statement.

Save the @@ROWCOUNT in a local variable in order to use it later in code:

EXEC (@SQL);

DECLARE @RowCount int = @@ROWCOUNT;

IF @RowCount &gt; 0
BEGIN
    SET @RowFlag = 1;
END;

PRINT @RowCount;