USE [uuap_nn_test]
GO
/****** Object: StoredProcedure [dbo].[proc_updateSecOrgWebCode] Script Date: 2018/10/12 10:10:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:Feily
-- Create date: 2018-08-20
-- Description:维护更新数结构WebCode
-- =============================================
ALTER PROCEDURE [dbo].[proc_updateSecOrgWebCode]
AS
BEGIN
SET NOCOUNT ON;
-- 创建递归视图
WITH f_sec_org
AS
(
SELECT CAST(
dbo.fn_get_wbs_code (ROW_NUMBER() OVER(ORDER BY o.order_num))
AS VARCHAR(100)
) AS wbsCodeStr,
CAST(ROW_NUMBER() OVER(ORDER BY o.order_num) AS VARCHAR(100)) AS
wbsCode,
*
FROM sec_org o
WHERE o.parent_org_id IS NULL
UNION ALL
SELECT CAST(
fo.wbsCodeStr + '.' + dbo. fn_get_wbs_code(ROW_NUMBER() OVER(ORDER BY o.order_num))
AS VARCHAR(100)
) AS wbsCodeStr,
CAST(
fo.wbsCode + '.' + CONVERT(VARCHAR(100), ROW_NUMBER() OVER(ORDER BY o.order_num))
AS VARCHAR(100)
) AS wbsCode,
o.*
FROM f_sec_org fo
INNER JOIN sec_org o
ON fo.org_id = o.parent_org_id
)
-- 维护更新数结构WebCode
UPDATE sec_org
SET wbs_code = t.wbsCodeStr,
wbs_level = t.webLevel
FROM (
SELECT LEN(wbsCodeStr) / 4 AS webLevel,
*
FROM f_sec_org
) t
WHERE t.org_id = sec_org.org_id
END