SQL UPDATE语句关联表值函数(CROSS APPLY)关联更新数据

  SQL UPDATE语句关联表值函数(CROSS APPLY)关联更新数据

创建临时表：

SQL Code:

CREATE TABLE #Table
(
  [isid] [int] NOT NULL IDENTITY(1, 1),
  [ARAP] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
  [DepartmentCode] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
  [FMSDeptCode] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL,--集团部门编码
  [FMSSmallGroupCode] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL,--三级小微编码
  [FMSSmallGroupName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL,--三级小微名称
 )
  
  //来源:C/S框架网 | www.csframework.com | QQ:23404761

UPDATE语句关联表值函数(CROSS APPLY)关联更新数据：

SQL Code:

UPDATE #Table SET FMSDeptCode=b.FMSDeptCode,FMSSmallGroupCode=b.SmallGroupCode,FMSSmallGroupName=b.SmallGroupName
FROM #Table a CROSS APPLY dbo.ufn_FMS_GetDeptMapping(a.DepartmentCode,a.ARAP) b;


//来源:C/S框架网 | www.csframework.com | QQ:23404761

表值函数:

SQL Code:

CREATE FUNCTION [dbo].[ufn_FMS_GetDeptMapping]
(
    @DepartmentCode VARCHAR(50),--TMS部门编码
     @ARAP VARCHAR(2)--AR/AP
  )
  RETURNS @T TABLE
  (
    CompanyCode VARCHAR(50),
    TMSDeptCode VARCHAR(50),
    FMSDeptCode VARCHAR(50),
    SmallGroupCode VARCHAR(50),
    SmallGroupName NVARCHAR(50)
  )
  AS
  BEGIN
    
    
    --省略代码....
    
    END;
    
    //来源:C/S框架网 | www.csframework.com | QQ:23404761

---

扫一扫加微信