Getting @@ROWCOUNT when there is an insert query in SQL-SERVER? Hope you are doing well.
I have a procedure as you can see below :

ALTER Procedure [dbo].[OpenExcel]  
  @TableName varchar(2000),
  @FileName  varchar(2000),
  @ResultOpenExcel tinyint output

AS 
Begin

 Declare @Rowflag Bit=0
 Set     @ResultOpenExcel=0

   DECLARE @Provider Nvarchar(2000);
   SET @Provider = 'Microsoft.ACE.OLEDB.12.0';

   DECLARE @SQL Nvarchar(MAX)=
   ' SELECT * INTO #TTT
     from openrowset(''' + @Provider + ''',''Excel 8.0;Database=' + @FileName +';hdr=yes'+''',''select * from [Sheet1$]'' );

     insert into ' + @TableName+'
     select * 
     from #TTT
     drop Table #TTT'

   EXEC (@SQL)

IF(@@ROWCOUNT&gt;0)
SET @RowFlag=1  

Print @@Rowcount

if(@RowFlag=1)
               set @ResultOpenExcel=1
            else 
               set @ResultOpenExcel=0

               Return @ResultOpenExcel
END


The problem is that @@ROWCOUNT won't work for the insert statement I guess !!! How Can I solve this issue?
Thanks in advance
@@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;