Optionally SELECT TOP

Use dynamic SQL or IF. Using a variable like that will mess up your query plans.

A local variable value is not available to the optimizer when selecting a query plan. And different plans are needed for small and large values of a TOP VALUES clause.

DECLARE @var BIGINT = NULL;
SELECT TOP (CASE 
               WHEN @var IS NULL 
                  THEN (SELECT 2row_count 
                        FROM   sys.dm_db_partition_stats AS p 
                        WHERE  index_id < 2 
                               and object_id = OBJECT_ID('dbo.T_ADHERENT_ADR'))
                  ELSE @var
            END)
         
FROM dbo.TADHERENTADR

You could use SET ROWCOUNT instead of SELECT TOP (@var) ... as:

SET @var = ISNULL(@var, 0)
SET ROWCOUNT @var
SELECT * FROM ...
SET ROWCOUNT 0

set ROWCOUNT 0 means all rows.

You could use OFFSET/FETCH

Here is an example :

DECLARE 
    @RowsCount INT 
,   @Offset INT = 0
IF @RowsCount IS NULL
    SET  @RowsCount = (SELECT COUNT() FROM MyTable)
SELECT 
FROM MyTable
ORDER BY ColumnName
OFFSET @Offset ROWS 
FETCH NEXT @RowsCount ROWS ONLY;

basically, FETCH and OFFSET is used to paginate the results.