Can I avoid dynamic SQL? 1

[LNBruno]

I've got a database (2000) with 12 tables - one for each month (rolling 12).

I've got an ugly gob of dynamic SQL to get the job done of totalling for the last 12 (loop through temp table with tablenames + processed flag), but would LOVE to replace it with non-dynamic SQL.

Any ideas? I mean other than tracking down the genius who came up with this implementation and beating him/her to within an inch of his/her life?



< M!ke >

 

[bborissov]

Can you show what you do in that dynamic SQL?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[LNBruno]

Do I have to? I'm so embarrassed...

I'm using a table datatype to store the names of the date-based tables along with a ProcessedFlag. Then I loop through it, executing the same SQL statement, just changing the MonthNo & tablename (with YYYY suffix) like so:

SET @SQL = 'INSERT #Processing
  SELECT a.Account_No
         ', b.OpenAcctInd_'  + @MonthNo +
         ', b.ActiveAcctInd_'  + @MonthNo +
         ', SUM(b.SalesAmt)
         , SUM(b.Transactions)

-- truncated for brevity

    FROM #badTable a
    JOIN ' + @TableName + ' b
      ON b.ACCTNO = a.Account_No
GROUP BY a.Account_No
         , b.OpenAcctInd_'  + @MonthNo +
         ', b.ActiveAcctInd_'  + @MonthNo + ''

   EXEC (@SQL)

Does that give you an idea?



< M!ke >

 

[bborissov]

I am afraid you can't avoid it.
Field names are variables and the table name also.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[LNBruno]

I was afraid of that. Thanks, anyway, bb!

< M!ke >

 

[SQLDenis]

create a partitioned view

create view bla
as
select * from jantable
union all
select * from febtable
union all
select * from marchtable
....
....
etc etc


then select * from bla where (I assume you have a date column in the tables)

the only backdraw is the maintanance, you will have to update the view every time you create a new table

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[SQLDenis]

BTW you will have to add checks on the tables so that the partitioned view doesn't scan all the tables whe you do a select

so for the jan 2007 table the check will be date >= 20070101 and date < 20070201

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[LNBruno]

Thx Denis, but I've pretty much written this off as a lost cause... :-(

< M!ke >