set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
create function CreateRoute
(
@addresser int, --发件人
@goals int, --目标(收件人)
@TransmitMode int --发送方式
)
returns nvarchar(max)
as
declare @gDeptId int
declare @aDeptId int
declare @Route nvarchar(1000) --路径
declare @aRoute nvarchar(500) --发件人发送路径
declare @gRoute nvarchar(500) --收件人接收路径
declare @addresserDeptNo int -- 发件人部门层次
declare @goalsDeptNo int --目标的部门层次
declare @managers int --部门领导IDset @aDeptId=(select DeptId from Users where id=@addresser)
set @gDeptId=(select DeptId from Users where id=@goals)
set @aRoute=ltrim(str(@addresser)+',')
--print ltrim(@aRoute)
set @gRoute=ltrim(str(@goals))
--print @gRouteBEGIN
-- a
if @TransmitMode=1 --此方式为直接发送
set @Route=@aRoute + @gRoute
--print @Route
if @TransmitMode=2 --此方式按照层层审批的方式进行传递
begin
set @addresserDeptNo=(select DeptNo from Department where DeptId=@aDeptId)
set @goalsDeptNo=(select DeptNo from Department where DeptId=@gDeptId)
if @addresserDeptNo>@goalsDeptNo
while @addresserDeptNo>@goalsDeptNo --判断发件人部门DEPTNO与收件人DEPTNO的从属关系 如果addresser的DeptNo 大于 goals的DeptNo,那么表明发件人在组织架构中位于目标的下层
begin
set @managers=(select Manager from Department where DeptId=@aDeptId)
set @aRoute=@aRoute+ltrim(str(@managers)+',') --记录路径 即将上级部门主管记录下来
set @addresserDeptNo=(select DeptNo from Department where DeptId=(select ParentDept from Department where DeptId=@aDeptId)) --求得上级部门DeptNo
set @aDeptId=(select ParentDept from Department where DeptId=@aDeptId) --求得上级部门ID
end
else
while @addresserDeptNo<@goalsDeptNo
begin
set @managers=(select Manager from Department where DeptId=@aDeptId)
set @gRoute=ltrim(str(@managers)+',')+@gRoute
set @goalsDeptNo=(select DeptNo from Department where DeptId=(select ParentDept from Department where DeptId=@gDeptId))
set @gDeptId=(select ParentDept from Department where DeptId=@gDeptId)
end
if @addresserDeptNo=@goalsDeptNo
while @aDeptId<>@gDeptId
begin
set @managers=(select Manager from Department where DeptId=@aDeptId)
set @aRoute=@aRoute+ltrim(str(@managers)+',')
set @aDeptId=(select ParentDept from Department where DeptId=@aDeptId)
set @managers=(select Manager from Department where DeptId=@gDeptId)
set @gRoute=ltrim(str(@managers)+',')+@gRoute
set @gDeptId=(select ParentDept from Department where DeptId=@gDeptId)
end
if @aDeptId=@gDeptId
set @Route=@aRoute+@gRoute
end
--print @Route
return @Route
END
set QUOTED_IDENTIFIER ON
go
create function CreateRoute
(
@addresser int, --发件人
@goals int, --目标(收件人)
@TransmitMode int --发送方式
)
returns nvarchar(max)
as
declare @gDeptId int
declare @aDeptId int
declare @Route nvarchar(1000) --路径
declare @aRoute nvarchar(500) --发件人发送路径
declare @gRoute nvarchar(500) --收件人接收路径
declare @addresserDeptNo int -- 发件人部门层次
declare @goalsDeptNo int --目标的部门层次
declare @managers int --部门领导IDset @aDeptId=(select DeptId from Users where id=@addresser)
set @gDeptId=(select DeptId from Users where id=@goals)
set @aRoute=ltrim(str(@addresser)+',')
--print ltrim(@aRoute)
set @gRoute=ltrim(str(@goals))
--print @gRouteBEGIN
-- a
if @TransmitMode=1 --此方式为直接发送
set @Route=@aRoute + @gRoute
--print @Route
if @TransmitMode=2 --此方式按照层层审批的方式进行传递
begin
set @addresserDeptNo=(select DeptNo from Department where DeptId=@aDeptId)
set @goalsDeptNo=(select DeptNo from Department where DeptId=@gDeptId)
if @addresserDeptNo>@goalsDeptNo
while @addresserDeptNo>@goalsDeptNo --判断发件人部门DEPTNO与收件人DEPTNO的从属关系 如果addresser的DeptNo 大于 goals的DeptNo,那么表明发件人在组织架构中位于目标的下层
begin
set @managers=(select Manager from Department where DeptId=@aDeptId)
set @aRoute=@aRoute+ltrim(str(@managers)+',') --记录路径 即将上级部门主管记录下来
set @addresserDeptNo=(select DeptNo from Department where DeptId=(select ParentDept from Department where DeptId=@aDeptId)) --求得上级部门DeptNo
set @aDeptId=(select ParentDept from Department where DeptId=@aDeptId) --求得上级部门ID
end
else
while @addresserDeptNo<@goalsDeptNo
begin
set @managers=(select Manager from Department where DeptId=@aDeptId)
set @gRoute=ltrim(str(@managers)+',')+@gRoute
set @goalsDeptNo=(select DeptNo from Department where DeptId=(select ParentDept from Department where DeptId=@gDeptId))
set @gDeptId=(select ParentDept from Department where DeptId=@gDeptId)
end
if @addresserDeptNo=@goalsDeptNo
while @aDeptId<>@gDeptId
begin
set @managers=(select Manager from Department where DeptId=@aDeptId)
set @aRoute=@aRoute+ltrim(str(@managers)+',')
set @aDeptId=(select ParentDept from Department where DeptId=@aDeptId)
set @managers=(select Manager from Department where DeptId=@gDeptId)
set @gRoute=ltrim(str(@managers)+',')+@gRoute
set @gDeptId=(select ParentDept from Department where DeptId=@gDeptId)
end
if @aDeptId=@gDeptId
set @Route=@aRoute+@gRoute
end
--print @Route
return @Route
END
关键字 'declare' 附近有语法错误。
Route 好像是计算 转发路径
set QUOTED_IDENTIFIER ON
go
create function CreateRoute
(
@addresser int, --发件人
@goals int, --目标(收件人)
@TransmitMode int --发送方式
)
returns nvarchar(max)
as
BEGIN
declare @gDeptId int
declare @aDeptId int
declare @Route nvarchar(1000) --路径
declare @aRoute nvarchar(500) --发件人发送路径
declare @gRoute nvarchar(500) --收件人接收路径
declare @addresserDeptNo int -- 发件人部门层次
declare @goalsDeptNo int --目标的部门层次
declare @managers int --部门领导IDset @aDeptId=(select DeptId from Users where id=@addresser)
set @gDeptId=(select DeptId from Users where id=@goals)
set @aRoute=ltrim(str(@addresser)+',')
--print ltrim(@aRoute)
set @gRoute=ltrim(str(@goals))
--print @gRouteBEGIN
-- a
if @TransmitMode=1 --此方式为直接发送
set @Route=@aRoute + @gRoute
--print @Route
if @TransmitMode=2 --此方式按照层层审批的方式进行传递
begin
set @addresserDeptNo=(select DeptNo from Department where DeptId=@aDeptId)
set @goalsDeptNo=(select DeptNo from Department where DeptId=@gDeptId)
if @addresserDeptNo>@goalsDeptNo
while @addresserDeptNo>@goalsDeptNo --判断发件人部门DEPTNO与收件人DEPTNO的从属关系 如果addresser的DeptNo 大于 goals的DeptNo,那么表明发件人在组织架构中位于目标的下层
begin
set @managers=(select Manager from Department where DeptId=@aDeptId)
set @aRoute=@aRoute+ltrim(str(@managers)+',') --记录路径 即将上级部门主管记录下来
set @addresserDeptNo=(select DeptNo from Department where DeptId=(select ParentDept from Department where DeptId=@aDeptId)) --求得上级部门DeptNo
set @aDeptId=(select ParentDept from Department where DeptId=@aDeptId) --求得上级部门ID
end
else
while @addresserDeptNo<@goalsDeptNo
begin
set @managers=(select Manager from Department where DeptId=@aDeptId)
set @gRoute=ltrim(str(@managers)+',')+@gRoute
set @goalsDeptNo=(select DeptNo from Department where DeptId=(select ParentDept from Department where DeptId=@gDeptId))
set @gDeptId=(select ParentDept from Department where DeptId=@gDeptId)
end
if @addresserDeptNo=@goalsDeptNo
while @aDeptId<>@gDeptId
begin
set @managers=(select Manager from Department where DeptId=@aDeptId)
set @aRoute=@aRoute+ltrim(str(@managers)+',')
set @aDeptId=(select ParentDept from Department where DeptId=@aDeptId)
set @managers=(select Manager from Department where DeptId=@gDeptId)
set @gRoute=ltrim(str(@managers)+',')+@gRoute
set @gDeptId=(select ParentDept from Department where DeptId=@gDeptId)
end
if @aDeptId=@gDeptId
set @Route=@aRoute+@gRoute
end
--print @Route
return @Route
END
end
(
@addresser int, --发件人
@goals int, --目标(收件人)
@TransmitMode int --发送方式
)
returns nvarchar(max)
as
begin
declare @gDeptId int
... ....
end
(
@addresser int, --发件人
@goals int, --目标(收件人)
@TransmitMode int --发送方式
)
returns nvarchar(max)
as
begin
declare @gDeptId int
declare @aDeptId int
declare @Route nvarchar(1000) --路径
declare @aRoute nvarchar(500) --发件人发送路径
declare @gRoute nvarchar(500) --收件人接收路径
declare @addresserDeptNo int -- 发件人部门层次
declare @goalsDeptNo int --目标的部门层次
declare @managers int --部门领导ID set @aDeptId=(select DeptId from Users where id=@addresser)
set @gDeptId=(select DeptId from Users where id=@goals)
set @aRoute=ltrim(str(@addresser)+',')
--print ltrim(@aRoute)
set @gRoute=ltrim(str(@goals))
--print @gRoute BEGIN
-- a
if @TransmitMode=1 --此方式为直接发送
set @Route=@aRoute + @gRoute
--print @Route
if @TransmitMode=2 --此方式按照层层审批的方式进行传递
begin
set @addresserDeptNo=(select DeptNo from Department where DeptId=@aDeptId)
set @goalsDeptNo=(select DeptNo from Department where DeptId=@gDeptId)
if @addresserDeptNo>@goalsDeptNo
while @addresserDeptNo>@goalsDeptNo --判断发件人部门DEPTNO与收件人DEPTNO的从属关系 如果addresser的DeptNo 大于 goals的DeptNo,那么表明发件人在组织架构中位于目标的下层
begin
set @managers=(select Manager from Department where DeptId=@aDeptId)
set @aRoute=@aRoute+ltrim(str(@managers)+',') --记录路径 即将上级部门主管记录下来
set @addresserDeptNo=(select DeptNo from Department where DeptId=(select ParentDept from Department where DeptId=@aDeptId)) --求得上级部门DeptNo
set @aDeptId=(select ParentDept from Department where DeptId=@aDeptId) --求得上级部门ID
end
else
while @addresserDeptNo<@goalsDeptNo
begin
set @managers=(select Manager from Department where DeptId=@aDeptId)
set @gRoute=ltrim(str(@managers)+',')+@gRoute
set @goalsDeptNo=(select DeptNo from Department where DeptId=(select ParentDept from Department where DeptId=@gDeptId))
set @gDeptId=(select ParentDept from Department where DeptId=@gDeptId)
end
if @addresserDeptNo=@goalsDeptNo
while @aDeptId<>@gDeptId
begin
set @managers=(select Manager from Department where DeptId=@aDeptId)
set @aRoute=@aRoute+ltrim(str(@managers)+',')
set @aDeptId=(select ParentDept from Department where DeptId=@aDeptId)
set @managers=(select Manager from Department where DeptId=@gDeptId)
set @gRoute=ltrim(str(@managers)+',')+@gRoute
set @gDeptId=(select ParentDept from Department where DeptId=@gDeptId)
end
if @aDeptId=@gDeptId
set @Route=@aRoute+@gRoute
end
--print @Route
return @Route
END
end自定义函数 returns 返回类型 as 后面如果不是直接RETURN,首先要接begin 关键字.