数据库函数问题

[zunmaxiake1]

CREATE FUNCTION USP_GetOrgAccess
(
@p_userID int,
@p_elementCat varchar(50),
@p_elementCode int,
@p_MFcode varchar(50)
)
RETURNS
@tempOrgTree TABLE
(
[ItemID] [int] NOT NULL primary key,
[ParentID] [int] NULL,
[ItemName] [nvarchar](50) NULL,
[TypeID] [int] NULL,
[Enable] [bit] NOT NULL,
[ITEMORDER] [nvarchar](32) NULL
)
AS
begin
declare @v_strSql varchar(8000)
declare @v_allorg int
declare @v_localorg int
declare @v_suborg int

--v_tempLimit := 0;
set @v_strSql = ''
set @v_allorg = 0
set @v_localorg = 0
set @v_suborg = 0

--定义游标
--DECLARE CURSOR myCusor IS
--获取指用户定指定元素的常规组织权限范围

Select sum(DEPTLIMIT & 1),
sum(DEPTLIMIT & 2),
sum(DEPTLIMIT & 4)
into @v_allorg,@v_localorg,@v_suborg --这里语法错误
FROM TB_RoleLinkElement a
inner join v_tb_userrole b
on a.rolecode = b.ROLECODE
Where a.MFCode = @p_MFcode
and a.ELEMENTCAT = @p_elementCat
and a.ELEMENTCODE = @p_elementCode
and b.USERID = @p_userID;

if @v_allorg >= 1 then --这里附近有语法错误
select * from tb_orgtree;
else --这里附近有语法错误


--用户所在组织(当动作为“启动”时，部门范围为用户所在部门，其它动作需特别指定）
--if mod(v_tempLimit,4)>=2 then --这里附近有语法错误

if @v_localorg >= 2 or @p_elementCode = 1 then
@v_strSql = @v_strSql || '
insert into @tempOrgTree select OrgID from tb_orgldlnk where UserID = ' ||
@p_userID || '
union
select OrgID from tb_orgmemlnk where UserID = ' ||
@p_userID || '
';
end if;

--用户所在组织的下级组织
--if mod(v_tempLimit,8)>=4 then
if @v_suborg >= 4 then --这里附近有语法错误

if length(@v_strSql) <> 0 then --这里附近有语法错误

@v_strSql = @v_strSql || ' union all';
end if;
@v_strSql = @v_strSql || '
insert into @tempOrgTree select ItemID from tb_orgtree
start with parentid in
(
select OrgID from tb_orgldlnk where UserID = ' ||
@p_userID || '
union
select OrgID from tb_orgmemlnk where UserID = ' ||
@p_userID || '
)
connect by prior ItemID = ParentID
';
end if;

--自定义组织权限范围
if length(@v_strSql) <> 0 then
@v_strSql = @v_strSql || ' union ';
end if;
@v_strSql = @v_strSql || '
insert into @tempOrgTree select a.orgid
from tb_elementlinkorg a
inner join
(
select * from tb_rolelinkelement
where rolecode in
(
select rolecode from tb_rolelinkgroup where rolegroupcode in (select rolegroupcode from tb_rolegrouplinkuser where userid = ' ||
@p_userID || ')
union
select rolecode from tb_rolelinkuser where userid = ' ||
@p_userID || '
)
and MFCode = ''' || @p_MFcode || '''
and ELEMENTCAT = ''' || @p_elementCat || '''
and ELEMENTCODE = ''' || @p_elementCode || '''
) b
on a.rolelinkelementid = b.LinkID
where mod((a.Deptlimit+4-BITAND(a.Deptlimit,4)),4)>=2
union
select ItemID from tb_orgtree
start with parentid in
(
select a.orgid
from tb_elementlinkorg a
inner join
(
select * from tb_rolelinkelement
where rolecode in
(
select rolecode from tb_rolelinkgroup where rolegroupcode in (select rolegroupcode from tb_rolegrouplinkuser where userid = ' ||
@p_userID || ')
union
select rolecode from tb_rolelinkuser where userid = ' ||
@p_userID || '
)
and MFCode = ''' || @p_MFcode || '''
and ELEMENTCAT = ''' || @p_elementCat || '''
and ELEMENTCODE = ''' || @p_elementCode || '''
) b
on a.rolelinkelementid = b.LinkID
where mod((a.Deptlimit+4-BITAND(a.Deptlimit,8)),8)>=4
)
connect by prior ItemID = ParentID
';

@v_strSql = 'insert into @tempOrgTree select * from tb_orgtree where ItemID in (' || @v_strSql || ')';
exec @v_strSql;
end if; --这里附近有语法错误

end --uSP_GetOrgAccess;

这个函数问题很多，不知道怎么改，望指点！！！

[H_Gragon]

你可以挑些重点说一下，别一下一大片代码~！

[zunmaxiake1]

开始理解错了，这是个表值函数，要把最后处理的数据RETURNS @tempOrgTree TABLE 返回就可以了，这些数据不用给临时变量@v_strSql，直接insert就可以了。但是现在start with 还有connect附近报语法错误。

[zunmaxiake1]

Start with connect by prior 递归
这个是oracle的递归sql语法，怎样换成sqlserver的递归

[bdmh]

都是基本的语法问题，你去对着sqlserver的帮助看

[zunmaxiake1]

我看了，好像没有问题啊！！！

[快溜]

Select @v_allorg=sum(DEPTLIMIT & 1), @v_localorg=sum(DEPTLIMIT & 2), @v_suborg=sum(DEPTLIMIT & 4) FROM TB_RoleLinkElement a ................. 去掉then用begin end

[sunny6281]

CREATE FUNCTION USP_GetOrgAccess ( @p_userID int, @p_elementCat varchar(50), @p_elementCode int, @p_MFcode varchar(50) ) RETURNS @tempOrgTree TABLE ( [ItemID] [int] NOT NULL primary key, [ParentID] [int] NULL, [ItemName] [nvarchar](50) NULL, [TypeID] [int] NULL, [Enable] [bit] NOT NULL, [ITEMORDER] [nvarchar](32) NULL ) AS begin --declare @v_strSql varchar(8000) -- 拼sql要用nvarchar，最好设为max declare @v_strSql nvarchar(max) declare @v_allorg int declare @v_localorg int declare @v_suborg int --v_tempLimit := 0; --set @v_strSql = '' set @v_strSql = N'' set @v_allorg = 0 set @v_localorg = 0 set @v_suborg = 0 --定义游标 --DECLARE CURSOR myCusor IS --获取指用户定指定元素的常规组织权限范围 Select @v_allorg = sum(DEPTLIMIT & 1), @v_localorg = sum(DEPTLIMIT & 2), @v_suborg = sum(DEPTLIMIT & 4) --into @v_allorg,@v_localorg,@v_suborg --这里语法错误 FROM TB_RoleLinkElement a inner join v_tb_userrole b on a.rolecode = b.ROLECODE Where a.MFCode = @p_MFcode and a.ELEMENTCAT = @p_elementCat and a.ELEMENTCODE = @p_elementCode and b.USERID = @p_userID; --if @v_allorg >= 1 then --这里附近有语法错误 if @v_allorg >= 1 begin select * from tb_orgtree; end else --这里附近有语法错误 -- 这里不用管 前面改了 这里自然就好了 --用户所在组织(当动作为“启动”时，部门范围为用户所在部门，其它动作需特别指定） --if mod(v_tempLimit,4)>=2 then --这里附近有语法错误 --if @v_localorg >= 2 or @p_elementCode = 1 then if @v_localorg >= 2 or @p_elementCode = 1 begin -- 这里你到底想插到那个表啊？表名呢？ --@v_strSql = @v_strSql || ' --insert into @tempOrgTree --select OrgID from tb_orgldlnk where UserID = ' || -- @p_userID || ' --union --select OrgID from tb_orgmemlnk where UserID = ' || -- @p_userID || ' '; --end if; end --用户所在组织的下级组织 --if mod(v_tempLimit,8)>=4 then --if @v_suborg >= 4 then --这里附近有语法错误 if @v_suborg >= 4 begin --if length(@v_strSql) <> 0 then --这里附近有语法错误 if length(@v_strSql) <> 0 begin --@v_strSql = @v_strSql || ' union all'; @v_strSql = @v_strSql + N' union all'; --end if; -- 后面的懒得改了，你确定你学过sql吗？ @v_strSql = @v_strSql || ' insert into @tempOrgTree select ItemID from tb_orgtree start with parentid in ( select OrgID from tb_orgldlnk where UserID = ' || @p_userID || ' union select OrgID from tb_orgmemlnk where UserID = ' || @p_userID || ' ) connect by prior ItemID = ParentID '; end if; --自定义组织权限范围 if length(@v_strSql) <> 0 then @v_strSql = @v_strSql || ' union '; end if; @v_strSql = @v_strSql || ' insert into @tempOrgTree select a.orgid from tb_elementlinkorg a inner join ( select * from tb_rolelinkelement where rolecode in ( select rolecode from tb_rolelinkgroup where rolegroupcode in (select rolegroupcode from tb_rolegrouplinkuser where userid = ' || @p_userID || ') union select rolecode from tb_rolelinkuser where userid = ' || @p_userID || ' ) and MFCode = ''' || @p_MFcode || ''' and ELEMENTCAT = ''' || @p_elementCat || ''' and ELEMENTCODE = ''' || @p_elementCode || ''' ) b on a.rolelinkelementid = b.LinkID where mod((a.Deptlimit+4-BITAND(a.Deptlimit,4)),4)>=2 union select ItemID from tb_orgtree start with parentid in ( select a.orgid from tb_elementlinkorg a inner join ( select * from tb_rolelinkelement where rolecode in ( select rolecode from tb_rolelinkgroup where rolegroupcode in (select rolegroupcode from tb_rolegrouplinkuser where userid = ' || @p_userID || ') union select rolecode from tb_rolelinkuser where userid = ' || @p_userID || ' ) and MFCode = ''' || @p_MFcode || ''' and ELEMENTCAT = ''' || @p_elementCat || ''' and ELEMENTCODE = ''' || @p_elementCode || ''' ) b on a.rolelinkelementid = b.LinkID where mod((a.Deptlimit+4-BITAND(a.Deptlimit,8)),8)>=4 ) connect by prior ItemID = ParentID '; @v_strSql = 'insert into @tempOrgTree select * from tb_orgtree where ItemID in (' || @v_strSql || ')'; exec @v_strSql; end if; --这里附近有语法错误 end --uSP_GetOrgAccess;