CREATE function [USERID].[fn_userid](
@userid as bigint,
@roleid as nvarchar(500)
)
returns @tb table(
id int PRIMARY KEY
)
as
begin
with temptb(id,parentAgent,roleid)
as(
select id,parentAgent,roleid from USERID.useridKEY with (nolock) where parentAgent=@userid and @roleid not like '%,'+CONVERT(nvarchar(10),[roleid])+',%'
union all
select e.id,e.parentAgent,e.roleid from USERID.useridKEY as e with (nolock) join temptb as t
on e.parentAgent=t.id where @roleid not like '%,'+CONVERT(nvarchar(10),e.roleid)+',%'
)
insert into @tb select id from temptb
insert into @tb select @userid
return
end
GO
@userid as bigint,
@roleid as nvarchar(500)
)
returns @tb table(
id int PRIMARY KEY
)
as
begin
with temptb(id,parentAgent,roleid)
as(
select id,parentAgent,roleid from USERID.useridKEY with (nolock) where parentAgent=@userid and @roleid not like '%,'+CONVERT(nvarchar(10),[roleid])+',%'
union all
select e.id,e.parentAgent,e.roleid from USERID.useridKEY as e with (nolock) join temptb as t
on e.parentAgent=t.id where @roleid not like '%,'+CONVERT(nvarchar(10),e.roleid)+',%'
)
insert into @tb select id from temptb
insert into @tb select @userid
return
end
GO