Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Changing from "hard-coded" to dynamic query Expand / Collapse
Author
Message
Posted Friday, January 25, 2013 9:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 20, 2014 9:52 AM
Points: 19, Visits: 49
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/



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.
Post #1411803
Posted Friday, January 25, 2013 9:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 20, 2014 9:52 AM
Points: 19, Visits: 49
Wow, 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.
Post #1411806
Posted Friday, January 25, 2013 9:53 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:21 PM
Points: 13,083, Visits: 11,918
momba (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/



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.


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.

declare @sql nvarchar(max)

set @sql = 'select * from sys.databases order by name'

exec(@sql)

exec sp_executesql @sql

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.


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1411810
Posted Friday, January 25, 2013 11:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 20, 2014 9:52 AM
Points: 19, Visits: 49
Sean Lange (1/25/2013)
momba (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/



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.


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 ...


The database has a lot of tables. The TableCurrent and Table_2007_2008, ..., Table_2011_2012 are just the few that I need.
Post #1411858
Posted Friday, January 25, 2013 11:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 20, 2014 9:52 AM
Points: 19, Visits: 49
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/


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?
Post #1411862
Posted Friday, January 25, 2013 11:46 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:48 PM
Points: 23,015, Visits: 31,536
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/


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.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1411876
Posted Friday, January 25, 2013 12:45 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:21 PM
Points: 13,083, Visits: 11,918
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/


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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1411902
Posted Friday, January 25, 2013 1:03 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:48 PM
Points: 23,015, Visits: 31,536
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/


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


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 ] }
]




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1411910
Posted Friday, January 25, 2013 1:10 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:21 PM
Points: 13,083, Visits: 11,918
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):

[quote]

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.

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1411911
Posted Friday, January 25, 2013 1:13 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:48 PM
Points: 23,015, Visits: 31,536
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):

[quote]

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.

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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1411913
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse