CTE and RECURSIVE SQL Using SQL SERVER 2005 EXPRESS

/*
大概將近十年沒使用 SQL Server 開發系統了。
一直到最近有個新案子，它的後端需要使用SQL Server 2005 Express資料庫，我才有機會和動力去摸摸它。
‧SQL Server 2005 Express 與過去幾個版本一樣，非常容易上手，
‧它的oSQL(sqlcmd)功能還算強，但還是沒有像 DB2 與 Oracle 功能來得強大。
‧它的幾個extended stored procedures一樣好用(雖然有幾個因為安全性問題被移除，迂迴一下也可以做到)
‧OPENROWSET 等這類功能做得真好
‧終於有了 CTE 與 Recursive SQL 的語法，不過語法有追隨 DB2 的嫌疑。
　而且有多此一舉的感覺...LOG下來以免被我遺忘

Scenario: 使用 Recursive 產生日曆資料
*/

/*　建立測試 Table　*/

CREATE TABLE D_LIST
(
　DT VARCHAR(8)
)


/*
　使用CTE搭配Recursive SQL產生2009年日曆資料
　1.將MAXRECURSION 0表示不設限，否則會有以下錯誤
　　　Msg 530, Level 16, State 1, Line 1
　　　陳述式已結束。最大遞迴 100 已在陳述式完成之前用盡。
　2.INSERT 語法擺放的位置與 DB2 CTE 不同
*/

WITH N(DT)
AS
(SELECT '20090101'
UNION ALL
SELECT CONVERT(VARCHAR(8),DATEADD(D,1,CONVERT(DATETIME,DT,112)),112)
FROM N
WHERE CONVERT(VARCHAR(4),DATEADD(D,1,CONVERT(DATETIME,DT,112)),112) = '2009'
)
INSERT INTO D_LIST
SELECT * FROM N
OPTION (MAXRECURSION 0);


Dynamic SQL in procedure using SQL Server 2000

/* interact with dos using xp_cmdshell */

exec master..xp_cmdshell 'echo hello > c:\file.txt'
exec master..xp_cmdshell 'echo appended data >> c:\file.txt'
exec master..xp_cmdshell 'echo more data >> c:\file.txt'
master..xp_cmdshell 'bcp master..sysobjects out c:\file.bcp -S -U -P -c '

/* using dynamic sql */

declare @cmd varchar(1000)
select @cmd = 'osql -U -P -S -Q"select * from master..sysobjects" -o"c:\osqloutput.txt" -w500'
exec master..xp_cmdshell @cmd

if exists(select 1 from sysobjects
where name = 'sp_reindex' and type = 'P' )
drop procedure sp_reindexgo
create procedure sp_reindexasbegin
declare @cmd varchar(255)
declare ic insensitive cursor for
select 'dbcc dbreindex (' + name + ')'
from sysobjects
where type = 'U'
open ic
fetch next from ic into @cmd
while @@fetch_status = 0
begin exec (@cmd)
fetch next from ic into @cmd end
close ic
deallocate ic
end