Sean Lange (1/25/2013)
Depending on how often you will be running this query you might consider using sp_executesql instead of the more generic exec. It will cache your execution plan and more importantly it allows for parameters.http://www.sqlskills.com/blogs/kimberly/exec-and-sp_executesql-how-are-they-different/[/url]
This:
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';