Post reply

Changing from "hard-coded" to dynamic query

  • January 25, 2013 at 7:36 am

    #267706

    Hi. My name is Momba and I'm a T-SQL noob....

    So I've got the following hard-coded query:

    select * TableCurrent

    WHERE BRANCHID='950' AND (DISC_DTE IS NULL OR DISC_DTE > 20070630)

    union

    select * from Table_2007_2008

    WHERE BRANCHID='950' AND (DISC_DTE IS NULL OR DISC_DTE > 20070630)

    union

    select * from Table_2008_2009

    WHERE BRANCHID='950' AND (DISC_DTE IS NULL OR DISC_DTE > 20070630)

    union

    select * from Table_2009_2010

    WHERE BRANCHID='950' AND (DISC_DTE IS NULL OR DISC_DTE > 20070630)

    union

    select * from Table_2010_2011

    WHERE BRANCHID='950' AND (DISC_DTE IS NULL OR DISC_DTE > 20070630)

    union

    select * from Table_2011_2012

    WHERE BRANCHID='950' AND (DISC_DTE IS NULL OR DISC_DTE > 20070630)

    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:

    Use TableCurrent

    Declare @loopYrbeg int

    Declare @loopYrend int

    Declare @tablename sysname

    Declare @SQL varchar(MAX)

    Set @loopYrbeg = 2007

    begin

    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

    end

    exec (@sql)

    select * TableCurrent

    WHERE BRANCHID='950' AND (DISC_DTE IS NULL OR DISC_DTE > 20070630)

    end

    ...Unfortunately my best attempt doesn't work as it should. Please advise. Thank you.

  • January 25, 2013 at 8:04 am

    #1581056

    This will fix what you have:

    Declare @loopYrbeg int

    Declare @loopYrend int

    Declare @tablename sysname

    Declare @SQL varchar(MAX)

    Set @loopYrbeg = 2007

    Set @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

    end

    set @SQL = left(@sql, LEN(@sql) - 6)

    print (@sql)

    end

    ______________________________________________________________________________
    How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

  • January 25, 2013 at 8:34 am

    #1581074

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

  • January 25, 2013 at 8:52 am

    #1581082

    Just initialize the string with that query, like so:

    Declare @loopYrbeg int

    Declare @loopYrend int

    Declare @tablename sysname

    Declare @SQL varchar(MAX)

    Set @loopYrbeg = 2007

    Set @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;

    end

    set @SQL = left(@sql, LEN(@sql) - 6);

    print (@sql);

    ______________________________________________________________________________
    How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

  • January 25, 2013 at 9:02 am

    #1581092

    Ohhhhh (duh). That makes sense.

    Thank you!!!! 😀

  • January 25, 2013 at 9:16 am

    #1581098

    This:

    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;

  • January 25, 2013 at 9:20 am

    #1581100

    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/%5B/url%5D

    _______________________________________________________________

    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 9:25 am

    #1581104

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

  • January 25, 2013 at 9:31 am

    #1581106

    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/%5B/url%5D

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

  • January 25, 2013 at 9:31 am

    #1581107

    momba (1/25/2013)

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

    No loops required.

  • January 25, 2013 at 9:33 am

    #1581109

    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/%5B/url%5D

    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.

  • January 25, 2013 at 9:38 am

    #1581112

    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.

  • January 25, 2013 at 9:53 am

    #1581116

    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/%5B/url%5D

    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 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 11:13 am

    #1581156

    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/%5B/url%5D

    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.

  • January 25, 2013 at 11:18 am

    #1581160

    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/%5B/url%5D

    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?

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy