InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 14:34:16 GMT20http://www.sqlservercentral.com/Forums/Topic1411721-3077-1.aspxThis modification will make sure you include only actually existing tables and do not miss any of them:[code="sql"]declare @SQLCmd nvarchar(max), @Params nvarchar(max), @DiscDate datetime ;SET @DiscDate = '20090630';set @Params = N'@BranchID varchar(10), @DiscDate date';select @SQLCmd = ISNULL(@SQLCmd, 'select * from TableCurrent where branchid = @BranchID and (disc_dte is null or disc_dte > @DiscDate)') + char(13) + char(10) + 'UNION ALL ' + char(13) + char(10) + 'select * from ' + QUOTENAME(O.NAME ) + ' where branchid = @BranchID and (disc_dte is null or disc_dte > @DiscDate)'-- SELECT * FROM sys.objects AS OWHERE O.type = 'U' AND O.name LIKE 'Table[_]20[0-1][0-9]%[_]20[01][0-9]'print @SQLCmd;exec sp_executesql @SQLCmd, @params = @Params, @BatchID = '950', @DiscDate = @DiscDate;[/code]And if you want to avoid unnecessary scanning too far in the history you may add an extra condition into the WHERE clause.It will include only those tables which can actually contain records from the date period you're interersted in:[code="sql"]declare @SQLCmd nvarchar(max), @Params nvarchar(max), @DiscDate datetime ;SET @DiscDate = '20090630';set @Params = N'@BranchID varchar(10), @DiscDate date';select @SQLCmd = ISNULL(@SQLCmd, 'select * from TableCurrent where branchid = @BranchID and (disc_dte is null or disc_dte > @DiscDate)') + char(13) + char(10) + 'UNION ALL ' + char(13) + char(10) + 'select * from ' + QUOTENAME(O.NAME ) + ' where branchid = @BranchID and (disc_dte is null or disc_dte > @DiscDate)'-- SELECT * FROM sys.objects AS OWHERE O.type = 'U' AND O.name LIKE 'Table[_]20[0-1][0-9]%[_]20[01][0-9]' AND O.name >= 'Table_' + CONVERT(nvarchar(128), YEAR(@DiscDate)-1)print @SQLCmd;exec sp_executesql @SQLCmd, @params = @Params, @BatchID = '950', @DiscDate = @DiscDate;[/code]Tue, 12 Feb 2013 19:22:32 GMTSergiyhttp://www.sqlservercentral.com/Forums/Topic1411721-3077-1.aspx[quote][b]Lynn Pettis (1/25/2013)[/b][hr][quote][b]Sean Lange (1/25/2013)[/b][hr][quote][b]Lynn Pettis (1/25/2013)[/b][hr]Sean,@BatchID should have been @BranchID in the sp_executesql statement. That was a type.Regarding @params = @Params, quite legal. From MSDN (emphasis is mine, and yes the names can be the same):[quote]sp_executesql [ @stmt = ] statement[ { ,[b] [ @params = ][/b] N'@parameter_name data_type [ OUT | OUTPUT ][ ,...n ]' } { , [ @param1 = ] 'value1' [ ,...n ] }] [/quote]Lynn,I figured the Branch/Batch was a typo. After I posted I realized it was pretty much a direct copy from what you posted (typo included). The @params = @Params looked so strange to me I just assumed it wouldn't work. The worst part of this is that not catching the typo is a clear indication that the OP does not have a good grasp of what is going on here. :w00t:OP - you need to truly understand the code here before you use it. You are the one who has to support it when you get that call at 3am. I don't think your boss will be too impressed if you tell them you don't understand the code because you used what some person on the internet suggested.[/quote]Actually the OP did catch the typo regarding @BatchID/@BranchID.[/quote]Good grief!!! I wish it was morning so I could blame lack of coffee...I will blame it on the anticipation of getting a few beers after work today (being Friday)./me creeps backwards from this one before I make even more of a fool out of myself. :-DFri, 25 Jan 2013 13:25:27 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1411721-3077-1.aspx[quote][b]Sean Lange (1/25/2013)[/b][hr][quote][b]Lynn Pettis (1/25/2013)[/b][hr]Sean,@BatchID should have been @BranchID in the sp_executesql statement. That was a type.Regarding @params = @Params, quite legal. From MSDN (emphasis is mine, and yes the names can be the same):[quote]sp_executesql [ @stmt = ] statement[ { ,[b] [ @params = ][/b] N'@parameter_name data_type [ OUT | OUTPUT ][ ,...n ]' } { , [ @param1 = ] 'value1' [ ,...n ] }] [/quote]Lynn,I figured the Branch/Batch was a typo. After I posted I realized it was pretty much a direct copy from what you posted (typo included). The @params = @Params looked so strange to me I just assumed it wouldn't work. The worst part of this is that not catching the typo is a clear indication that the OP does not have a good grasp of what is going on here. :w00t:OP - you need to truly understand the code here before you use it. You are the one who has to support it when you get that call at 3am. I don't think your boss will be too impressed if you tell them you don't understand the code because you used what some person on the internet suggested.[/quote]Actually the OP did catch the typo regarding @BatchID/@BranchID.Fri, 25 Jan 2013 13:13:32 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1411721-3077-1.aspx[quote][b]Lynn Pettis (1/25/2013)[/b][hr]Sean,@BatchID should have been @BranchID in the sp_executesql statement. That was a type.Regarding @params = @Params, quite legal. From MSDN (emphasis is mine, and yes the names can be the same):[quote]sp_executesql [ @stmt = ] statement[ { ,[b] [ @params = ][/b] N'@parameter_name data_type [ OUT | OUTPUT ][ ,...n ]' } { , [ @param1 = ] 'value1' [ ,...n ] }] [/quote]Lynn,I figured the Branch/Batch was a typo. After I posted I realized it was pretty much a direct copy from what you posted (typo included). The @params = @Params looked so strange to me I just assumed it wouldn't work. The worst part of this is that not catching the typo is a clear indication that the OP does not have a good grasp of what is going on here. :w00t:OP - you need to truly understand the code here before you use it. You are the one who has to support it when you get that call at 3am. I don't think your boss will be too impressed if you tell them you don't understand the code because you used what some person on the internet suggested.Fri, 25 Jan 2013 13:10:50 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1411721-3077-1.aspx[quote][b]Sean Lange (1/25/2013)[/b][hr][quote][b]momba (1/25/2013)[/b][hr][quote][b]Lynn Pettis (1/25/2013)[/b][hr][quote][b]Sean Lange (1/25/2013)[/b][hr]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.[url=http://www.sqlskills.com/blogs/kimberly/exec-and-sp_executesql-how-are-they-different/]http://www.sqlskills.com/blogs/kimberly/exec-and-sp_executesql-how-are-they-different/[/url][/quote]This:[code="sql"]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';[/code][/quote]I got "Msg 208, Level 16, State 1, Line 1 Invalid object name 'Table_2007_2008'." as an error message.Also, on line "exec sp_executesql @SQLCmd, @params = @Params, @BatchID = '950', @DiscDate = '20070630';" should that say @BranchID instead of @BatchID?[/quote]This has a fundamental flaw. @params = @Params. You built this as a parameter but then you are trying to assign it??? You also have at least one parameter that is missing (@BranchID). It is referenced in the dynamic sql but it has not had a value assigned so it will be NULL in your dynamic query.Taking a shot in the dark I think your exec should be [code]exec sp_executesql @SQLCmd, @Params, @BranchID = '950', @DiscDate = '20070630';[/code]So I have to ask if you have ever heard of table partitioning. I can't say for sure because I don't much of anything about your system but given the table names I suspect it could be very handy for you.[url=http://msdn.microsoft.com/en-us/library/ms190787.aspx]http://msdn.microsoft.com/en-us/library/ms190787.aspx[/url][/quote]Sean,@BatchID should have been @BranchID in the sp_executesql statement. That was a typo.Regarding @params = @Params, quite legal. From MSDN (emphasis is mine, and yes the names can be the same):[quote]sp_executesql [ @stmt = ] statement[ { ,[b] [ @params = ][/b] N'@parameter_name data_type [ OUT | OUTPUT ][ ,...n ]' } { , [ @param1 = ] 'value1' [ ,...n ] }] [/quote]Fri, 25 Jan 2013 13:03:37 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1411721-3077-1.aspx[quote][b]momba (1/25/2013)[/b][hr][quote][b]Lynn Pettis (1/25/2013)[/b][hr][quote][b]Sean Lange (1/25/2013)[/b][hr]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.[url=http://www.sqlskills.com/blogs/kimberly/exec-and-sp_executesql-how-are-they-different/]http://www.sqlskills.com/blogs/kimberly/exec-and-sp_executesql-how-are-they-different/[/url][/quote]This:[code="sql"]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';[/code][/quote]I got "Msg 208, Level 16, State 1, Line 1 Invalid object name 'Table_2007_2008'." as an error message.Also, on line "exec sp_executesql @SQLCmd, @params = @Params, @BatchID = '950', @DiscDate = '20070630';" should that say @BranchID instead of @BatchID?[/quote]This has a fundamental flaw. @params = @Params. You built this as a parameter but then you are trying to assign it??? You also have at least one parameter that is missing (@BranchID). It is referenced in the dynamic sql but it has not had a value assigned so it will be NULL in your dynamic query.Taking a shot in the dark I think your exec should be [code]exec sp_executesql @SQLCmd, @Params, @BranchID = '950', @DiscDate = '20070630';[/code]So I have to ask if you have ever heard of table partitioning. I can't say for sure because I don't much of anything about your system but given the table names I suspect it could be very handy for you.[url=http://msdn.microsoft.com/en-us/library/ms190787.aspx]http://msdn.microsoft.com/en-us/library/ms190787.aspx[/url]Fri, 25 Jan 2013 12:45:19 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1411721-3077-1.aspx[quote][b]momba (1/25/2013)[/b][hr][quote][b]Lynn Pettis (1/25/2013)[/b][hr][quote][b]Sean Lange (1/25/2013)[/b][hr]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.[url=http://www.sqlskills.com/blogs/kimberly/exec-and-sp_executesql-how-are-they-different/]http://www.sqlskills.com/blogs/kimberly/exec-and-sp_executesql-how-are-they-different/[/url][/quote]This:[code="sql"]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';[/code][/quote]I got "Msg 208, Level 16, State 1, Line 1 Invalid object name 'Table_2007_2008'." as an error message.Also, on line "exec sp_executesql @SQLCmd, @params = @Params, @BatchID = '950', @DiscDate = '20070630';" should that say @BranchID instead of @BatchID?[/quote]Last question first, yes, @BatchID was a typo.Regarding the rest, with nothing to test against you get what you get. Take what has been provided and modify what needs to modified to correct for possible errors.Fri, 25 Jan 2013 11:46:30 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1411721-3077-1.aspx[quote][b]Lynn Pettis (1/25/2013)[/b][hr][quote][b]Sean Lange (1/25/2013)[/b][hr]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.[url=http://www.sqlskills.com/blogs/kimberly/exec-and-sp_executesql-how-are-they-different/]http://www.sqlskills.com/blogs/kimberly/exec-and-sp_executesql-how-are-they-different/[/url][/quote]This:[code="sql"]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';[/code][/quote]I got "Msg 208, Level 16, State 1, Line 1 Invalid object name 'Table_2007_2008'." as an error message.Also, on line "exec sp_executesql @SQLCmd, @params = @Params, @BatchID = '950', @DiscDate = '20070630';" should that say @BranchID instead of @BatchID?Fri, 25 Jan 2013 11:18:07 GMTmombahttp://www.sqlservercentral.com/Forums/Topic1411721-3077-1.aspx[quote][b]Sean Lange (1/25/2013)[/b][hr][quote][b]momba (1/25/2013)[/b][hr][quote][b]Sean Lange (1/25/2013)[/b][hr]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.[url=http://www.sqlskills.com/blogs/kimberly/exec-and-sp_executesql-how-are-they-different/]http://www.sqlskills.com/blogs/kimberly/exec-and-sp_executesql-how-are-they-different/[/url][/quote]It has to run daily. I looked over some of the sp_executesql documentation and it does seem like a good fit for what I'm doing. There's just a learning curve on the syntax.[/quote]For what you are doing I am not sure you need to parameterize your sql. The syntax when using parameters is a little strange at first but once you do it a few times it gets easier. If you are not needing ...[/quote]The database has a lot of tables. The TableCurrent and Table_2007_2008, ..., Table_2011_2012 are just the few that I need.Fri, 25 Jan 2013 11:13:47 GMTmombahttp://www.sqlservercentral.com/Forums/Topic1411721-3077-1.aspx[quote][b]momba (1/25/2013)[/b][hr][quote][b]Sean Lange (1/25/2013)[/b][hr]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.[url=http://www.sqlskills.com/blogs/kimberly/exec-and-sp_executesql-how-are-they-different/]http://www.sqlskills.com/blogs/kimberly/exec-and-sp_executesql-how-are-they-different/[/url][/quote]It has to run daily. I looked over some of the sp_executesql documentation and it does seem like a good fit for what I'm doing. There's just a learning curve on the syntax.[/quote]For what you are doing I am not sure you need to parameterize your sql. The syntax when using parameters is a little strange at first but once you do it a few times it gets easier. If you are not needing to parameterize your sql the syntax is almost identical. The following will get a list of all databases on your server. In your case the dynamic string would be more complicated but in the end it is just a select statement.[code]declare @sql nvarchar(max)set @sql = 'select * from sys.databases order by name'exec(@sql)exec sp_executesql @sql[/code]Check out this link from Gail's blog. It has an awesome example of using dynamic sql with parameters. I realize the topic is not relevant to your current situation but the article is well worth reading anyway. We all have to create these types of queries at some point. :-)Fri, 25 Jan 2013 09:53:32 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1411721-3077-1.aspxWow, before I even post my reply you've answered my question.(you're like a real Jedi knight)...I'm going to try your method now.Fri, 25 Jan 2013 09:38:29 GMTmombahttp://www.sqlservercentral.com/Forums/Topic1411721-3077-1.aspx[quote][b]Sean Lange (1/25/2013)[/b][hr]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.[url=http://www.sqlskills.com/blogs/kimberly/exec-and-sp_executesql-how-are-they-different/]http://www.sqlskills.com/blogs/kimberly/exec-and-sp_executesql-how-are-they-different/[/url][/quote]It has to run daily. I looked over some of the sp_executesql documentation and it does seem like a good fit for what I'm doing. There's just a learning curve on the syntax.Fri, 25 Jan 2013 09:33:35 GMTmombahttp://www.sqlservercentral.com/Forums/Topic1411721-3077-1.aspx[quote][b]momba (1/25/2013)[/b][hr]... I came across one last issue. How can I make the " While @loopYrbeg < year ( getdate() )" statement conditional?More specifically IF month ( getdate() ) < 7 THEN @loopYrbeg < year ( getdate() ) - 1 ELSE @loopYrbeg < year ( getdate() )[/quote]No loops required.Fri, 25 Jan 2013 09:31:50 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1411721-3077-1.aspx[quote][b]Sean Lange (1/25/2013)[/b][hr]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.[url=http://www.sqlskills.com/blogs/kimberly/exec-and-sp_executesql-how-are-they-different/]http://www.sqlskills.com/blogs/kimberly/exec-and-sp_executesql-how-are-they-different/[/url][/quote]This:[code="sql"]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';[/code]Fri, 25 Jan 2013 09:31:29 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1411721-3077-1.aspx... I came across one last issue. How can I make the " While @loopYrbeg < year ( getdate() )" statement conditional?More specifically IF month ( getdate() ) < 7 THEN @loopYrbeg < year ( getdate() ) - 1 ELSE @loopYrbeg < year ( getdate() )Fri, 25 Jan 2013 09:25:55 GMTmombahttp://www.sqlservercentral.com/Forums/Topic1411721-3077-1.aspxDepending 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.[url=http://www.sqlskills.com/blogs/kimberly/exec-and-sp_executesql-how-are-they-different/]http://www.sqlskills.com/blogs/kimberly/exec-and-sp_executesql-how-are-they-different/[/url]Fri, 25 Jan 2013 09:20:21 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1411721-3077-1.aspxThis:[code="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;[/code]Fri, 25 Jan 2013 09:16:35 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1411721-3077-1.aspxOhhhhh (duh). That makes sense.Thank you!!!! :-DFri, 25 Jan 2013 09:02:16 GMTmombahttp://www.sqlservercentral.com/Forums/Topic1411721-3077-1.aspxJust initialize the string with that query, like so:[code="sql"]Declare @loopYrbeg intDeclare @loopYrend intDeclare @tablename sysnameDeclare @SQL varchar(MAX)Set @loopYrbeg = 2007Set @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;endset @SQL = left(@sql, LEN(@sql) - 6);print (@sql);[/code]Fri, 25 Jan 2013 08:52:26 GMTtoddasdhttp://www.sqlservercentral.com/Forums/Topic1411721-3077-1.aspxYes, this is getting really close (thank you)... but how do I union the current table with all the archives?i.e. select * TableCurrent WHERE BRANCHID='950' AND (DISC_DTE IS NULL OR DISC_DTE > 20070630...which doesn't have a fiscal year in its name.Fri, 25 Jan 2013 08:34:47 GMTmombahttp://www.sqlservercentral.com/Forums/Topic1411721-3077-1.aspxThis will fix what you have:[code="sql"]Declare @loopYrbeg intDeclare @loopYrend intDeclare @tablename sysnameDeclare @SQL varchar(MAX)Set @loopYrbeg = 2007Set @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 endset @SQL = left(@sql, LEN(@sql) - 6)print (@sql)end[/code]Fri, 25 Jan 2013 08:04:22 GMTtoddasdhttp://www.sqlservercentral.com/Forums/Topic1411721-3077-1.aspxHi. My name is Momba and I'm a T-SQL noob....So I've got the following hard-coded query:[code="sql"]select * TableCurrent WHERE BRANCHID='950' AND (DISC_DTE IS NULL OR DISC_DTE > 20070630)unionselect * from Table_2007_2008WHERE BRANCHID='950' AND (DISC_DTE IS NULL OR DISC_DTE > 20070630)unionselect * from Table_2008_2009WHERE BRANCHID='950' AND (DISC_DTE IS NULL OR DISC_DTE > 20070630)unionselect * from Table_2009_2010WHERE BRANCHID='950' AND (DISC_DTE IS NULL OR DISC_DTE > 20070630)unionselect * from Table_2010_2011WHERE BRANCHID='950' AND (DISC_DTE IS NULL OR DISC_DTE > 20070630)unionselect * from Table_2011_2012WHERE BRANCHID='950' AND (DISC_DTE IS NULL OR DISC_DTE > 20070630)[/code]At the end of each fiscal year (June 30th) a new archive is made of the current table and I end up having to dig through everything to get what I need. And this is my best attempt to making it dynamic:[code="sql"]Use TableCurrentDeclare @loopYrbeg intDeclare @loopYrend intDeclare @tablename sysnameDeclare @SQL varchar(MAX)Set @loopYrbeg = 2007begin 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 endexec (@sql)select * TableCurrent WHERE BRANCHID='950' AND (DISC_DTE IS NULL OR DISC_DTE > 20070630)end[/code]...Unfortunately my best attempt doesn't work as it should. Please advise. Thank you.Fri, 25 Jan 2013 07:36:18 GMTmomba