#副標=SQL 2005搶先導覽
#大標=Transact-SQL新體驗
#眉標=SQL 2005
#作者=文／李明儒



---------------------box 程式1------------------
--如果CTE的WITH不在第一列, 前方要加上;
;WITH DIYParts_BOM
(PartName, Parent, Level, SortCol) 
AS
(
    --Recursive CTE分為兩個部分, 
	--第一部分為Anchor Member
    --指不會被遞迴呼叫到的部分
	SELECT PartName, Parent, 0, 
	CONVERT(nvarchar(128),PartNo)
    FROM DIYParts
    WHERE Parent=N'ROOT'

    UNION ALL

	--UNION ALL後方的部分稱為
	--Recursive Member, 會在遞迴
    --呼叫過程中反覆執行, 直到
	--無任何查詢結果為止
	SELECT P.PartName, P.Parent, 
	B.Level+1, 
    CONVERT(nvarchar(128),
      B.SortCol+'-'+
      CONVERT(nvarchar(128),P.PartNo))
	FROM DIYParts P, DIYParts_BOM B
	WHERE P.Parent=B.PartName
)
SELECT 
REPLICATE('       ',Level) + PartName,
Level,SortCol
FROM DIYParts_BOM
ORDER BY SortCol 
------------------------end----------------------------




--------------------------box 程式2------------------------
#新舊例外處理寫法的比較

--傳統@@ERROR例外處理的寫法
INSERT INTO nothing VALUES ('1','Jeffrey')
--發生錯誤時的@@ERROR只能讀取一次, 
--若要做多次判斷, 得另存入變數中
DECLARE @errCode INT
SET @errCode=@@ERROR
IF @errCode=2627 
    PRINT 'PK constraint'
IF @errCode=515
	PRINT 'Not null constraint'
INSERT INTO nothing VALUES ('2',NULL)
--每個指令後方都要加上同樣的處理流程
SET @errCode=@@ERROR
IF @errCode=2627 
    PRINT 'PK constraint'
IF @errCode=515
	PRINT 'Not null constraint'
GO
--SQL 2005之TRY...CATCH式寫法
BEGIN TRY
	INSERT INTO nothing VALUES ('1','Jeffrey')
	INSERT INTO nothing VALUES ('2',NULL)
END TRY
BEGIN CATCH
	IF ERROR_NUMBER()=2627 
		PRINT 'PK constraint'
	IF ERROR_NUMBER()=515 
		PRINT 'Not null constraint'
END CATCH
--------------------------end----------------------------




---------------------box 程式3------------------------
CREATE TABLE TB1 (
	ID INT NOT NULL PRIMARY KEY,
	DATA_T NTEXT,
	DATA_NV NVARCHAR(MAX)
)
GO
INSERT INTO TB1 VALUES (
	1,'BOOK','BOOK'
)
GO
DECLARE @T NTEXT
--錯誤: 無法宣告NTEXT作為區域變數
SELECT @T=DATA_T FROM TB1
--以上的寫法不可行
GO
--在SQL 2005中, NVARCHAR(MAX)可當區域變數
DECLARE @NV NVARCHAR(MAX)
SELECT @NV=DATA_NV FROM TB1 WHERE ID=1
PRINT @NV
--------------------------end---------------------------




------------------------box 程式4--------------------------
#OUTPUT功能展示，結果如圖10：

CREATE TABLE TB2 (
    ID INT NOT NULL PRIMARY KEY,
	NAME NVARCHAR(16),
	AGE INT
)
GO
INSERT INTO TB2 VALUES (1,'George',18) 
GO
DECLARE @T1 AS TABLE (
	ID INT,NAME NVARCHAR(16),AGE INT
)
INSERT INTO TB2
	OUTPUT INSERTED.* INTO @T1
	VALUES (2,'Mary',16) 
SELECT * FROM @T1
GO
DECLARE @T2 AS TABLE (
	OID INT,ONAME NVARCHAR(16),OAGE INT,
	NID INT,NNAME NVARCHAR(16),NAGE INT
)
UPDATE TB2 SET AGE=20
OUTPUT DELETED.*, INSERTED.* INTO @T2
SELECT * FROM @T2
GO
DECLARE @T3 AS TABLE (
	ID INT,NAME NVARCHAR(16),AGE INT
)
DELETE FROM TB2 
OUTPUT DELETED.* INTO @T3
SELECT * FROM @T3
GO
--------------------end---------------------