根据用户E号查询销售区域

来源：互联网发布：船舶钢材用量计算法编辑：程序博客网时间：2024/04/28 22:50

-- Description: 根据用户E号查询销售区域
-- Params:
-- @UserNumber:用户E号
-- Demo:
-- select * from sfa_f_GetSaleAreasByUserNumber(706416)
-- =============================================
ALTER FUNCTION [dbo].[sfa_f_GetSaleAreasByUserNumber]
(
@UserNumber INT
)
RETURNS TABLE
AS
RETURN
(

--1.查询销售区域

WITH TBasSaleArea as

(

   --1.1 查找父节点

   select

tbsa.SaleAreaID,

tbsa.SaleAreaCode,

tbsa.SaleAreaName,

tbsa.SaleAreaKey,

tbsa.PSaleAreaID

   from sfa_t_TBasSaleArea tbsa

   inner join sfa_t_TBasSaleAreaDepart tbsad on tbsad.SaleAreaID=tbsa.SaleAreaID

   inner join com_f_GetDepartmentsByUserNumber(@UserNumber) dept on dept.departmentid=tbsad.DepartmentID

   where tbsa.Status=1
),
TBasSaleArea_2 as (
  select

ctbsa.SaleAreaID,

ctbsa.SaleAreaCode,

ctbsa.SaleAreaName,

ctbsa.SaleAreaKey,

ctbsa.PSaleAreaID

   from sfa_t_TBasSaleArea ctbsa

   inner join TBasSaleArea ptbsa on ptbsa.SaleAreaID=ctbsa.PSaleAreaID

   where ctbsa.Status=1
),
TBasSaleArea_3 as(
  select

ctbsa.SaleAreaID,

ctbsa.SaleAreaCode,

ctbsa.SaleAreaName,

ctbsa.SaleAreaKey,

ctbsa.PSaleAreaID

   from sfa_t_TBasSaleArea ctbsa

   inner join TBasSaleArea_2 ptbsa on ptbsa.SaleAreaID=ctbsa.PSaleAreaID

   where ctbsa.Status=1
)
--返回结果值
select distinct * from TBasSaleArea
union all
select distinct * from TBasSaleArea_2
union all
select distinct * from TBasSaleArea_3

)

GO

0 0