select * TableCurrent 
WHERE BRANCHID='950' AND (DISC_DTE IS NULL OR DISC_DTE > 20070630)
union
select * from Table_2007_2008
WHERE BRANCHID='950' AND (DISC_DTE IS NULL OR DISC_DTE > 20070630)
union
select * from Table_2008_2009
WHERE BRANCHID='950' AND (DISC_DTE IS NULL OR DISC_DTE > 20070630)
union
select * from Table_2009_2010
WHERE BRANCHID='950' AND (DISC_DTE IS NULL OR DISC_DTE > 20070630)
union
select * from Table_2010_2011
WHERE BRANCHID='950' AND (DISC_DTE IS NULL OR DISC_DTE > 20070630)
union
select * from Table_2011_2012
WHERE BRANCHID='950' AND (DISC_DTE IS NULL OR DISC_DTE > 20070630)

Use TableCurrent

Declare @loopYrbeg int
Declare @loopYrend int
Declare @tablename sysname
Declare @SQL varchar(MAX)

Set @loopYrbeg = 2007

begin

        While @loopYrbeg < year( getdate() )

		begin

		  Set @loopYrend = @loopYrbeg + 1
		  Set @tablename = 'Table_' + CONVERT(varchar(4), @loopYrbeg) + '_' + CONVERT(varchar(4), @loopYrend)
		  Set @sql = 'select *
		              from ' + @tablename +char(10)+
		             'WHERE BRANCHID='950' AND (DISC_DTE IS NULL OR DISC_DTE > 20070630)'+char(10)+
		             'union'+char(10)

			  Set @loopYrbeg = @loopYrbeg + 1

		end

exec (@sql)

select * TableCurrent 
WHERE BRANCHID='950' AND (DISC_DTE IS NULL OR DISC_DTE > 20070630)

end

Declare @loopYrbeg int
Declare @loopYrend int
Declare @tablename sysname
Declare @SQL varchar(MAX)

Set @loopYrbeg = 2007
Set @sql = ''

begin
	While @loopYrbeg < year( getdate() )
	begin
		Set @loopYrend = @loopYrbeg + 1
		Set @tablename = 'Table_' + CONVERT(varchar(4), @loopYrbeg) + '_' + CONVERT(varchar(4), @loopYrend)
		Set @sql = @sql + 'select * from ' + @tablename +char(10)+
				 'WHERE BRANCHID=''950'' AND (DISC_DTE IS NULL OR DISC_DTE > 20070630)' + char(10) +
				 'union' + char(10)
		Set @loopYrbeg = @loopYrbeg + 1
	end
set @SQL = left(@sql, LEN(@sql) - 6)
print (@sql)

end

Declare @loopYrbeg int
Declare @loopYrend int
Declare @tablename sysname
Declare @SQL varchar(MAX)

Set @loopYrbeg = 2007
Set @sql = 'select * from tableCurrent ' + char(10) +
				 'WHERE BRANCHID=''950'' AND (DISC_DTE IS NULL OR DISC_DTE > 20070630)' + char(10) +
				 'union' + char(10);

While @loopYrbeg < year( getdate() )
begin
	Set @loopYrend = @loopYrbeg + 1;
	Set @tablename = 'Table_' + CONVERT(varchar(4), @loopYrbeg) + '_' + CONVERT(varchar(4), @loopYrend);
	Set @sql = @sql + 'select * from ' + @tablename +char(10)+
			 'WHERE BRANCHID=''950'' AND (DISC_DTE IS NULL OR DISC_DTE > 20070630)' + char(10) +
			 'union' + char(10);
	Set @loopYrbeg = @loopYrbeg + 1;
end
set @SQL = left(@sql, LEN(@sql) - 6);
print (@sql);

declare @StartYear int = 2007,
        @SQLCmd nvarchar(max);

with quickTally(n) as (select top(year(dateadd(mm,6,getdate())) - @StartYear - 1) n from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))dt(n))
select
    @SQLCmd = stuff((select char(13) + char(10) +
                            'select * from Table_' +
                            cast(@StartYear + n as varchar) + '_' + cast(@StartYear + n + 1 as varchar) +
                            ' where branchid = ''950'' and (disc_dte is null or disc_dte > ''20070630'') union all'
                     from
                         quickTally
                     for xml path(''),TYPE).value('.','nvarchar(max)'),1,2,'') +
              char(13) + char(10) + 'select * from TableCurrent where branchid = ''950'' and (disc_dte is null or disc_dte > ''20070630'');'
;
print @SQLCmd;

declare @StartYear int = 2007,
        @SQLCmd nvarchar(max),
        @Params nvarchar(max);
set @Params = N'@BranchID varchar(10), @DiscDate date');

with quickTally(n) as (select top(year(dateadd(mm,6,getdate())) - @StartYear - 1) n from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))dt(n))
select
    @SQLCmd = stuff((select char(13) + char(10) +
                            'select * from Table_' +
                            cast(@StartYear + n as varchar) + '_' + cast(@StartYear + n + 1 as varchar) +
                            ' where branchid = @BranchID and (disc_dte is null or disc_dte > @DiscDate) union all'
                     from
                         quickTally
                     for xml path(''),TYPE).value('.','nvarchar(max)'),1,2,'') +
              char(13) + char(10) + 'select * from TableCurrent where branchid = @BranchID and (disc_dte is null or disc_dte > @DiscDate);'
;
print @SQLCmd;

exec sp_executesql @SQLCmd, @params = @Params, @BatchID = '950', @DiscDate = '20070630';