動態語法 Part 4 : Final 完整範例－藍色小惡魔

drop table ICMS30Bak.dbo.JobBak_V2_繳款記錄
select * into ICMS30Bak.dbo.JobBak_V2_繳款記錄 from confer.dbo.V2_繳款記錄

-- 從債協專用更新最新繳款記錄 - vSQL1

insert into confer.dbo.V2_繳款記錄 (cust_idn)
select distinct cust_idn
from confer.dbo.V2_繳款記錄_債協專用 where cust_idn not in (select cust_idn from confer.dbo.V2_繳款記錄)

declare @vSQL1 nvarchar(1000), @vSQL2 nvarchar(1000)
declare @ColumnName varchar(50), @RunDate datetime, @MaxDate datetime, @DateStr char(8)

-- 取得 Table 中以年月設計的欄位最大值 (考慮未來維護的人只知道增加欄位而未修改程式)
select @MaxDate = convert(char(8), convert(int, max(substring(col.name, 8, 4)) + '25') + 19110000)
from sysobjects obj join syscolumns col on obj.id = col.id
where obj.name = 'v2_繳款記錄' and col.name like 'INCOME_%'

set @vSQL1 = 'update V2_繳款記錄 set FIRST_PAY_DATE = V1.FIRST_PAY_DATE'
set @vSQL2 = 'update V2_繳款記錄 set first_in_date = dateadd(day, 15, first_pay_date) where (0=1'

set @RunDate = @MaxDate
while @RunDate >= '2006-02-01' begin

set @ColumnName = 'INCOME_' + convert(char(4), (datepart(year, @RunDate) - 1911) * 100 + datepart(month, @RunDate))
set @vSQL1 = @vSQL1 + ', ' + @ColumnName + ' = V1.' + @ColumnName
set @vSQL2 = @vSQL2 + ' or upper(' + @ColumnName + ') = ''Y'''

set @RunDate = dateadd(month, -1, @RunDate)
continue end

set @vSQL1 = @vSQL1 + ' from V2_繳款記錄 V2 join V2_繳款記錄_債協專用 V1 on V1.CUST_IDN = V2.CUST_IDN'
print @vSQL1
execute sp_executesql @vSQL1

update V2_繳款記錄 set last_in_date = null, first_in_date = null, times = 0
set @vSQL2 = @vSQL2 + ')'
print @vSQL2
execute sp_executesql @vSQL2

set @RunDate = @MaxDate
while @RunDate > '2006-02-01' begin
set @DateStr = convert(char(8), @RunDate, 112)

set @vSQL1 = 'update V2_繳款記錄 set times = times + 1,'
+ ' last_in_date = case when last_in_date is null then ''' + @DateStr + ''' else last_in_date end'
+ ' from V2_繳款記錄 where income_' + convert(char(4), convert(int, substring(@DateStr, 1, 6)) - 191100)
+ ' = ''Y'''
print @vSQL1
execute sp_executesql @vSQL1

set @RunDate = dateadd(month, -1, @RunDate)
continue end

引用自:藍色小惡魔《用 SQL 語法動態組合 SQL》
http://www.imp.idv.tw/play/forum/viewthread?thread=743

這種操死 User 的作法當然不是長久之計但是也不能操死自己所以只好玩 SQL 填補需求裂口