I am trying to loop through my counter to create a dynamic sql query which should finally look like I am trying to loop through my counter to create a dynamic sql query which should finally look like 

I am trying to use this approach to get the final query but does not seems to work

declare @CurrentRow int
set @CurrentRow =0;
declare @RowsToProcess int
declare @FinalHistoricalQuery varchar(5000)
WHILE @CurrentRow&lt;3
BEGIN      
   SET @FinalHistoricalQuery =' select 11'+convert(varchar(20),@CurrentRow) + ' union '
   SET @CurrentRow=@CurrentRow+1
END

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


The final output that i am looking for is 110 111 112 but it comes as 112 Any suggestion would be helpful.
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)