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
(
[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
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
(
@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
扫一扫加微信
版权声明:本文为开发框架文库发布内容,转载请附上原文出处连接
NewDoc C/S框架网