January 25, 2013 at 11:46 am
momba (1/25/2013)
Lynn Pettis (1/25/2013)
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';
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?
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.
January 25, 2013 at 12:45 pm
momba (1/25/2013)
Lynn Pettis (1/25/2013)
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';
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?
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
exec sp_executesql @SQLCmd, @params, @BranchID = '950', @DiscDate = '20070630';
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.
http://msdn.microsoft.com/en-us/library/ms190787.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 25, 2013 at 1:03 pm
Sean Lange (1/25/2013)
momba (1/25/2013)
Lynn Pettis (1/25/2013)
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';
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?
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
exec sp_executesql @SQLCmd, @params, @BranchID = '950', @DiscDate = '20070630';
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.
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):
sp_executesql [ @stmt = ] statement
[
{ , [ @params = ] N'@parameter_name data_type [ OUT | OUTPUT ][ ,...n ]' }
{ , [ @param1 = ] 'value1' [ ,...n ] }
]
January 25, 2013 at 1:10 pm
Lynn Pettis (1/25/2013)
@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):
sp_executesql [ @stmt = ] statement
[
{ , [ @params = ] N'@parameter_name data_type [ OUT | OUTPUT ][ ,...n ]' }
{ , [ @param1 = ] 'value1' [ ,...n ] }
]
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.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 25, 2013 at 1:13 pm
Sean Lange (1/25/2013)
Lynn Pettis (1/25/2013)
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):
sp_executesql [ @stmt = ] statement
[
{ , [ @params = ] N'@parameter_name data_type [ OUT | OUTPUT ][ ,...n ]' }
{ , [ @param1 = ] 'value1' [ ,...n ] }
]
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.
Actually the OP did catch the typo regarding @BatchID/@BranchID.
January 25, 2013 at 1:25 pm
Lynn Pettis (1/25/2013)
Sean Lange (1/25/2013)
Lynn Pettis (1/25/2013)
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):
sp_executesql [ @stmt = ] statement
[
{ , [ @params = ] N'@parameter_name data_type [ OUT | OUTPUT ][ ,...n ]' }
{ , [ @param1 = ] 'value1' [ ,...n ] }
]
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.
Actually the OP did catch the typo regarding @BatchID/@BranchID.
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. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 12, 2013 at 7:22 pm
This modification will make sure you include only actually existing tables and do not miss any of them:
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 O
WHERE 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;
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:
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 O
WHERE 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 for TallyGenerator
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply