versatile dynamic code generator

We have columns in the table.  We want to get a sum of the rows in that column so that we can quickly check for changes, such as import/export.  Actually want the sum, and not a check-sum or hashcode solution.  OK, so sum(columnname) right?  Well, we have 60 or so columns, multiple tables with a similar format, and i DON’T want to hand code each one.

Fortunately, they are formful, as in “something_date_somethingelse” so i have a shot at coding it.  Would be fairly easy to use powershell, but alas, i lost it in the move.  That was a joke.

Dynamic SQL, since we are writing SQL code, is a good option.  Even if i don’t want to immediately run the code with exec(@SQLCode), i can generate the query, and store it, or run it manually when i want.  

The column names run from t_200906 to t_201405.  WTF?  i can work with the “t_’ part, but how do write logic to do an entire year (2010, 2011, 2012, 2013) but not all of 2009 and 2014 and how do i put this into the query?  Well, i don’t and i won’t.  I will generate a date temp table, containing the meta dates, with an id field, running from 1 to 72 and do all of the years time all 12 month.  THEN i will deal with the logic of getting out the right dates.  OK, so make the temp table.  

create table #myyears (
[each] [int],
[yearMonth] [varchar](10)
)

Gee, wasn’t THAT easy.  OK, wisenheimer, put the data in it.

Create a couple of local variables, and create a couple of nested while look.  Start at 2009 and increment the year counter until it is more than 2014.  Inside that loop, cycle from 1 to 12.  Add the two numbers…. ah poop.  If i add 2009+12, i get 2021, which is NOT 200912.  So i need to cast or convert int into varchar() so that i can stick them together.  Oh, remember also that we want 200901 and not 20091 for the first month.  The details are below: 

declare @mycounter int
declare @myyear int
declare @mymonth int
set @mymonth = 1
set @mycounter = 1
set @myyear = 2009
while @myyear < 2015
begin
while @mymonth< 13
begin
INSERT INTO #myyears ( each, yearMonth)
select @myCounter,
case when @mymonth < 10
          then cast(@myyear as varchar(10)) + '0' + cast(@mymonth as varchar(10)
else 
          cast(@myyear as varchar(10)) + cast(@mymonth as varchar(10))
end
set @mycounter = @mycounter+1
set @mymonth = @mymonth+1
end
set @myyear = @myyear+1
set @mymonth = 1
end
-- select * from #myYears where each <66 and each > 6

a little secret sauce and a little mea culpa.  You use the temp table by using “each” to index into the table to find the correct yearMonth.  By setting the limits of each correctly, you get just the right collection of dates.  The bad news is that the first time i wrote this, the years were ordered 100% wrong from the excel sheet i was supposed to update.  No sweat, i changed the order of the limits and decremented the internal counter rather than incrementing it.  But i forgot to change one of the GT\LTs and spent at least 10 minutes checking if the local variables were counting up and down correctly (they were) but i wan’t getting anything from the select so i wasn’t inserting any data.

NEXT, since we have the dates, we need to generate code that looks like 

select sum([n_200907]) as ‘200907’ FROM table.filename

but do it 59 times.  OK, 58 MORE selects.  

So we need to loop around a number of times, with the number of loop constrained by the two values of each, selecting the appropriate yearMonth from the temp table based on the value of “each”.  That is straightforward and we just have to keep beating on the loop until we get something that works.  And remember to initialize the @SQLCommand with “select” so we only get a select at the beginning, and append the from statement at the end after we leave the loop.  Looks like this

declare @SQLCommand varchar(5000)
set @SQLCommand = 'select'
declare @columnname varchar(100)
Declare @counter2 [int] = 65
while @counter2 > 6
begin
set @columnname = (select [yearMonth] from #myYears where each = @counter2)
set @SQLCommand = @SQLCommand + '
sum([t_' + @columnname + ']) as '''+ @columnname +''','
set @counter2 = @counter2-1
end
set @SQLCommand = @SQLCommand + ' FROM [Momentum].[dbo].[INS_TRX]'
print @SQLCommand

 

Advertisements