@@ROWCOUNT shows as 0 when deleting using dynamic query SQL

I am facing a trouble when using dynamic query and when trying to get the number of deleted records using @@ROWCOUNT

Here is my QUery

declare @query nvarchar(max)='delete from '+ @table_name + ' where kfh_id=' + cast(@kfh_id as varchar)
--print @query
exec (@query)
print @query
insert into tbl_cleanup_log (tablename,kfh_id,rows_affected,remark,deletiontime)
values(@table_name,@kfh_id,@@ROWCOUNT,@query,getdate())

Here after the dyanimic delete query (inside my cursor) I am trying to store the number of deleted records into another table using @@ROWCOUNT. But it shows as 0.

I didnt understand what I did wrong.

My SQL version is 2012

Answer

@@ROWCOUNT is working correctly. From the documentation:

Returns the number of rows affected by the last statement. If the number of rows is more than 2 billion, use ROWCOUNT_BIG.

The prior statement to the statement you use @@ROWCOUNT in is print @query and that returns no rows, and hence @@ROWCOUNT returns 0.

To fix this I would suggest PRINTing your dynamic statement first. Also you need to fix your dynamic statement so it isn’t open to injection. Don’t use the syntax EXEC (@SQL), use a parametrised call to sys.sp_executesql and ensure you properly delimit identify your dynamic object with QUOTENAME:

DECLARE @table_name sysname,
        @kfh_id int; --Guessed data type
DECLARE @query nvarchar(MAX) = N'delete from dbo.' + QUOTENAME(@table_name) + N' where kfh_id= @kfh_id;'; --Schema is guessed.

PRINT @query;

EXEC sys.sp_executesql @query, N'@kfh_id int', @kfh_id; --Reminder, guessed @kfh_id data type

INSERT INTO tbl_cleanup_log (tablename,
                             kfh_id,
                             rows_affected,
                             remark,
                             deletiontime)
VALUES (@table_name, @kfh_id, @@ROWCOUNT, @query, GETDATE());