I am trying to loop through my counter to create a dynamic sql query which should finally look like
You need to initialize @FinalHistoricalQuery before the loop and you need to use it in on the left side if the assignment in the loop.

declare @CurrentRow int
set @CurrentRow =0;
declare @RowsToProcess int
declare @FinalHistoricalQuery varchar(5000)

-- Set a start value
set @FinalHistoricalQuery = ''

WHILE @CurrentRow&lt;3
BEGIN      
                              -- Use variable here
   SET @FinalHistoricalQuery = @FinalHistoricalQuery + ' select 11'+convert(varchar(20),@CurrentRow) + ' union '
   SET @CurrentRow=@CurrentRow+1
END

SET @FinalHistoricalQuery = left(@FinalHistoricalQuery,len(@FinalHistoricalQuery)-6)

exec (@FinalHistoricalQuery)


Result:

-----------
110
111
112

(3 row(s) affected)