WITH expression_name [ ( column_name [,...n] ) ]
AS
( 
     --CTE_query_definition 
)

WITH tmpOrders AS
(
 SELECT EmployeeID, COUNT(*) as OrderCounts
  FROM   Orders
 GROUP BY EmployeeID 
)

 SELECT* FROM tmpOrders;

WITH expression_name AS [(ColName[,...n])]
(
    --CTE_query_definition
 
	-- 錨點部分
 
    UNION ALL
 
	-- 遞迴部分
)

-- 遞迴 Recursive CTE's
;WITH EmpCTE(EmployeeID, EmployeeFirstName, EmployeeLastName, MgrID, SalesLevel)
AS
( 

  -- 錨點部分 Anchor Member (AM) 
  SELECT EmployeeID, FirstName, LastName, ReportsTo, 0
  FROM Employees
  WHERE EmployeeID = 5

  UNION ALL
  
  -- 遞迴部分 Recursive Member (RM)
  SELECT 
		e.EmployeeID, e.FirstName, e.LastName, e.ReportsTo, m.SalesLevel+1
  FROM 
		Employees AS e
		INNER JOIN EmpCTE m ON e.ReportsTo = m.EmployeeID
)

-- 設定 MAXRECURSION 遞迴層次為 5
SELECT * FROM EmpCTE OPTION (MAXRECURSION 5)

-- 建立一個暫存表  等下查看
CREATE Table #tmp
(
  id  int IDENTITY(1,1) NOT NULL,
 EpTitle nvarchar(30)
);

-- 建立Cursor 並啟用了效能最佳化的 FORWARD_ONLY、READ_ONLY 資料指標
DECLARE tmpCursor CURSOR  FAST_FORWARD  FOR 
        (select EmployeeID,Title
        from Employees);
        
-- 宣告 Cursor 等下使用的變數
DECLARE @eid int,@title nvarchar(30);
 
OPEN tmpCursor--開啟Cursor

--將第一筆資料填入變數
FETCH NEXT FROM tmpCursor INTO @eid,@title

--  @@FETCH_STATUS 傳回值
--   0  FETCH 陳述式成功。
--  -1 FETCH 陳述式失敗，或資料列已超出結果集。
--  -2 遺漏提取的資料列。
WHILE @@FETCH_STATUS = 0
BEGIN
	 -- 進行想要處理的動作 
	INSERT INTO #tmp (EpTitle) VALUES(@title);
 
	-- 將下一筆資料填入變數 
    FETCH NEXT FROM tmpCursor INTO @eid,@title
END      

--關閉Cursor
CLOSE tmpCursor
--釋放Cursor
DEALLOCATE tmpCursor

-- 看一下剛剛處理後的結果
select * from #tmp

--DROP TABLE #tmp

CREATE TABLE #TableName
(
	columnName bigint
)

SELECT *  INTO #temp FROM Northwind.dbo.Employees

-- 全域暫存表 GTT
SELECT *  INTO ##temp FROM Northwind.dbo.Employees

-- 暫存表 TT
SELECT *  INTO #temp FROM Northwind.dbo.Employees

-- 表格變數 Tabel Variable
-- 變數名採用 @ 開頭
DECLARE @TableName TABLE 
(
	-- 欄位名稱  資料格式
	columnName bigint
)

--CREATE PROCEDURE dbo.SPRowIndexSample
--AS

-- 宣告 table 變數，此 @temp 資料為迴圈逐筆進行之資料

DECLARE @temp TABLE
(
	ID			INT ,  --ROW序號
	OrderID		INT,
	Employee	NVARCHAR(35)
);

-- @OutTable 為輸出暫存表
DECLARE @OutTable Table
(
  OrderID		INT,
	Employee	NVARCHAR(35)
);

-- 把資料塞到 @temp 變數裡面
INSERT INTO @temp (ID
, OrderID
, Employee)
	(SELECT
		ROW_NUMBER() OVER (ORDER BY OrderID) AS ID,
		OrderID,
		e.FirstName + '-' + e.LastName AS 'Employee'
	FROM Orders o
	INNER JOIN Employees e
		ON o.EmployeeID = e.EmployeeID
	WHERE o.OrderDate > '1998/5/1'
	)

-- 宣告變數
DECLARE
      -- @tmep 資料總筆數 
      @tempCount INT = ( SELECT
	COUNT(ID)
FROM @temp)
-- while 迴圈的逐筆資料 index
, @rowIndex int = 1
, @OrderId int
, @Employee nvarchar(35);

WHILE @rowIndex <= @tempCount
BEGIN

--透過 @tempCount ID 取得欲使用的欄位值
SELECT
	@OrderId = OrderID,
	@Employee = Employee
FROM @temp
WHERE ID = @rowIndex


-- 想要處理的動作 Start --
INSERT INTO @OutTable (OrderID
, Employee)
	VALUES (@OrderId, @Employee)

-- 想要處理的動作 End --

-- 設定 while 條件，跑下一筆
SET @rowIndex = @rowIndex + 1

END

-- 取出
SELECT
	OrderID,
	Employee
FROM @OutTable
--GO

--CREATE PROCEDURE dbo.SPStrSplitSample
--AS

-- 宣告字串變數，@strID 為資料來源的 OrderID 組合字串
-- @CurId 為迴圈進行的當下 OrderID
DECLARE @strOID NVARCHAR(2000), @CurOId NVARCHAR(10);

SET @strOID = '';
SELECT @strOID = @strOID + convert(VARCHAR, OrderID) + ','
FROM
  Orders
WHERE
  OrderDate > '1998/5/1';


-- @OutTable 為輸出暫存表
DECLARE @OutTable Table
(
  OrderID		INT,
	Employee	NVARCHAR(35)
);


-- 透過字串長度進行迴圈
WHILE LEN(@strOID) > 0
BEGIN

-- 擷取 OrderID
SET @CurOId = substring(@strOID, 1, charindex(',', @strOID, 1) - 1);


-- 想要處理的動作 Start --
INSERT INTO @OutTable (OrderID
                     , Employee)
SELECT OrderID
     , e.FirstName + '-' + e.LastName AS 'Employee'
FROM
  Orders o
  INNER JOIN Employees e
    ON o.EmployeeID = e.EmployeeID
WHERE
  OrderID = @CurOId;
-- 想要處理的動作 End --

-- 剃除完成的 OrederID，讓迴圈跑下一筆
SET @strOID = right(@strOID, len(@strOID) - charindex(',', @strOID, 1));

END

-- 取出
SELECT OrderID
     , Employee
FROM
  @OutTable
--GO

-- 接收預存程序回傳值
CREATE PROCEDURE SPDemo
(
    @Param1    int
   ,@Param2    varchar(100) OUTPUT  -- 回傳參數設定 OUTPUT
)
AS


IF ISNULL(@Param1,0)> 5
BEGIN
  SET @Param2 = 'the value is greather then 5 '
END

ELSE

BEGIN
  SET @Param2 = 'the value is less then or equal to 5 '
END

-- 回傳值
RETURN @Param1
GO

--==============================================

-- @OutputParameter 回傳參數
-- @ReturnValue 回傳值(僅能整數型別)
DECLARE @OutputParameter  varchar(100)
       ,@ReturnValue      int

EXEC @ReturnValue = SPDemo 8, @OutputParameter OUTPUT
PRINT @ReturnValue
PRINT @OutputParameter