Changing from "hard-coded" to dynamic query

Rate Topic

Display Mode

Topic Options

Author

Message

momba

Posted Friday, January 25, 2013 9:33 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, April 11, 2013 1:49 PM
Points: 15, Visits: 31

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

momba

Posted Friday, January 25, 2013 9:38 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, April 11, 2013 1:49 PM
Points: 15, Visits: 31

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

Sean Lange

Posted Friday, January 25, 2013 9:53 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 10:26 PM
Points: 8,606, Visits: 8,247

momba (1/25/2013)

Sean Lange (1/25/2013)

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

_______________________________________________________________

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

Post #1411810

momba

Posted Friday, January 25, 2013 11:13 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, April 11, 2013 1:49 PM
Points: 15, Visits: 31

Sean Lange (1/25/2013)

momba (1/25/2013)

Sean Lange (1/25/2013)

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.

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

momba

Posted Friday, January 25, 2013 11:18 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, April 11, 2013 1:49 PM
Points: 15, Visits: 31

Lynn Pettis (1/25/2013)

Sean Lange (1/25/2013)

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

Lynn Pettis

Posted Friday, January 25, 2013 11:46 AM

SSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:17 PM
Points: 21,617, Visits: 27,450

momba (1/25/2013)

Lynn Pettis (1/25/2013)

Sean Lange (1/25/2013)

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';

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.

Cool

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

Sean Lange

Posted Friday, January 25, 2013 12:45 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 10:26 PM
Points: 8,606, Visits: 8,247

momba (1/25/2013)

Lynn Pettis (1/25/2013)

Sean Lange (1/25/2013)

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';

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

Post #1411902

Lynn Pettis

Posted Friday, January 25, 2013 1:03 PM

SSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:17 PM
Points: 21,617, Visits: 27,450

Sean Lange (1/25/2013)

momba (1/25/2013)

Lynn Pettis (1/25/2013)

Sean Lange (1/25/2013)

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';

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

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

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1411910

Sean Lange

Posted Friday, January 25, 2013 1:10 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 10:26 PM
Points: 8,606, Visits: 8,247

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

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1411911

Lynn Pettis

Posted Friday, January 25, 2013 1:13 PM

SSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:17 PM
Points: 21,617, Visits: 27,450

Sean Lange (1/25/2013)

Lynn Pettis (1/25/2013)

Actually the OP did catch the typo regarding @BatchID/@BranchID.

Cool

Lynn Pettis

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1411913

« Prev Topic | Next Topic »