Popular Posts
Word break tag : <wbr/> (HTML5) The  HTML  <wbr>  tag  is  used  defines  a  potential  line  break  point  if  needed.  This  stands  for  Word  BReak. This  is  u... CTE, recursive search WITH DepartmentSearch(DeptID, DeptParent, DeptName, OuID) AS (     -- 找出簽核者所屬部門     SELECT d.DeptID, d.DeptParent, d.DeptName, d.OuID     FR... Create web service client cross SSL with eclipse When creating web service cross SSL using eclipse, it occuss some error like below: And it can't build the client source from this wa...
Stats
CTE, recursive search
WITH DepartmentSearch(DeptID, DeptParent, DeptName, OuID)
AS
(
    -- 找出簽核者所屬部門
    SELECT d.DeptID, d.DeptParent, d.DeptName, d.OuID
    FROM tbDepartment d
    LEFT JOIN tbEmpDept ed ON ed.DeptID = d.DeptID
    INNER JOIN tbFlowApprove fa ON fa.Approver = ed.EmpID
    WHERE fa.FlowInsID = @FlowInsID AND fa.FlowAct = @FlowAct AND fa.ActAppendix IS NULL
    UNION ALL
    -- 遞迴向上找出主管部門
    SELECT d.DeptID, d.DeptParent, d.DeptName, d.OuID
    FROM tbDepartment d
    INNER JOIN DepartmentSearch ON DepartmentSearch.DeptParent = d.DeptID
)
SELECT * FROM DepartmentSearch
other ref: 一般資料表運算式(Common Table Expressions, CTE)