这是我的存储过程:delimiter //CREATE PROCEDURE P_Mis_GetMenuAccess (in @UserID int)
BEGIN
SELECT temp.aid,t_Mis_Sys_Menu.* FROM
(
SELECT Access_LevelID AS aid,Access_MenuID AS meid FROM t_Mis_Sys_Useraccess WHERE Access_UserID=@UserID AND (select User_IsDeleted FROM t_Mis_Sys_User WHERE User_ID=@UserID )='F'
UNION
SELECT Access_Level AS aid,Access_Menu AS meid FROM t_Mis_Sys_RoleAccess WHERE Access_Role IN ( select Role_ID FROM t_Mis_Sys_Role WHERE (select User_IsDeleted FROM t_Mis_Sys_User WHERE User_ID=@UserID )='F' AND Role_ID IN (select Role_ID FROM t_Mis_Sys_UserRole WHERE User_ID=@UserID ) AND Role_ID IN (select Role_ID FROM t_Mis_Sys_Role WHERE Role_ID IN (select Role_id FROM t_Mis_Sys_UserRole WHERE User_ID=@UserID ) AND Role_IsDeleted='F'))
UNION
SELECT Com_LevelID AS aid,Com_MenuID AS meid FROM t_Mis_SysCommision WHERE Com_GetID=@UserID AND (select User_IsDeleted FROM t_Mis_Sys_User WHERE User_ID=@UserID )='F' AND now()>Com_StartTime AND now()<Com_EndTime
) AS temp INNER JOIN t_Mis_Sys_Menu ON meid=Menu_id;
END//总体是无法创建,1064错误,让我找第一行的错误。我的语句是可以查询成功的,如:
SELECT temp.aid,t_Mis_Sys_Menu.* FROM
(
SELECT Access_LevelID AS aid,Access_MenuID AS meid FROM t_Mis_Sys_Useraccess WHERE Access_UserID=1 AND (select User_IsDeleted FROM t_Mis_Sys_User WHERE User_ID=1 )='F'
UNION
SELECT Access_Level AS aid,Access_Menu AS meid FROM t_Mis_Sys_RoleAccess WHERE Access_Role IN ( select Role_ID FROM t_Mis_Sys_Role WHERE (select User_IsDeleted FROM t_Mis_Sys_User WHERE User_ID=1 )='F' AND Role_ID IN (select Role_ID FROM t_Mis_Sys_UserRole WHERE User_ID=1 ) AND Role_ID IN (select Role_ID FROM t_Mis_Sys_Role WHERE Role_ID IN (select Role_id FROM t_Mis_Sys_UserRole WHERE User_ID=1 ) AND Role_IsDeleted='F'))
UNION
SELECT Com_LevelID AS aid,Com_MenuID AS meid FROM t_Mis_SysCommision WHERE Com_GetID=1 AND (select User_IsDeleted FROM t_Mis_Sys_User WHERE User_ID=1 )='F' AND now()>Com_StartTime AND now()<Com_EndTime
) AS temp INNER JOIN t_Mis_Sys_Menu ON meid=Menu_id;大家帮我看看,感谢了
BEGIN
SELECT temp.aid,t_Mis_Sys_Menu.* FROM
(
SELECT Access_LevelID AS aid,Access_MenuID AS meid FROM t_Mis_Sys_Useraccess WHERE Access_UserID=@UserID AND (select User_IsDeleted FROM t_Mis_Sys_User WHERE User_ID=@UserID )='F'
UNION
SELECT Access_Level AS aid,Access_Menu AS meid FROM t_Mis_Sys_RoleAccess WHERE Access_Role IN ( select Role_ID FROM t_Mis_Sys_Role WHERE (select User_IsDeleted FROM t_Mis_Sys_User WHERE User_ID=@UserID )='F' AND Role_ID IN (select Role_ID FROM t_Mis_Sys_UserRole WHERE User_ID=@UserID ) AND Role_ID IN (select Role_ID FROM t_Mis_Sys_Role WHERE Role_ID IN (select Role_id FROM t_Mis_Sys_UserRole WHERE User_ID=@UserID ) AND Role_IsDeleted='F'))
UNION
SELECT Com_LevelID AS aid,Com_MenuID AS meid FROM t_Mis_SysCommision WHERE Com_GetID=@UserID AND (select User_IsDeleted FROM t_Mis_Sys_User WHERE User_ID=@UserID )='F' AND now()>Com_StartTime AND now()<Com_EndTime
) AS temp INNER JOIN t_Mis_Sys_Menu ON meid=Menu_id;
END//总体是无法创建,1064错误,让我找第一行的错误。我的语句是可以查询成功的,如:
SELECT temp.aid,t_Mis_Sys_Menu.* FROM
(
SELECT Access_LevelID AS aid,Access_MenuID AS meid FROM t_Mis_Sys_Useraccess WHERE Access_UserID=1 AND (select User_IsDeleted FROM t_Mis_Sys_User WHERE User_ID=1 )='F'
UNION
SELECT Access_Level AS aid,Access_Menu AS meid FROM t_Mis_Sys_RoleAccess WHERE Access_Role IN ( select Role_ID FROM t_Mis_Sys_Role WHERE (select User_IsDeleted FROM t_Mis_Sys_User WHERE User_ID=1 )='F' AND Role_ID IN (select Role_ID FROM t_Mis_Sys_UserRole WHERE User_ID=1 ) AND Role_ID IN (select Role_ID FROM t_Mis_Sys_Role WHERE Role_ID IN (select Role_id FROM t_Mis_Sys_UserRole WHERE User_ID=1 ) AND Role_IsDeleted='F'))
UNION
SELECT Com_LevelID AS aid,Com_MenuID AS meid FROM t_Mis_SysCommision WHERE Com_GetID=1 AND (select User_IsDeleted FROM t_Mis_Sys_User WHERE User_ID=1 )='F' AND now()>Com_StartTime AND now()<Com_EndTime
) AS temp INNER JOIN t_Mis_Sys_Menu ON meid=Menu_id;大家帮我看看,感谢了
@不是有效的变量名起始符。改一下变量名再试一下。
delimiter //CREATE PROCEDURE P_Mis_GetMenuAccess (in vUserID int)
BEGIN
SELECT temp.aid,t_Mis_Sys_Menu.* FROM
(
SELECT Access_LevelID AS aid,Access_MenuID AS meid FROM t_Mis_Sys_Useraccess WHERE Access_UserID=vUserID AND (select User_IsDeleted FROM t_Mis_Sys_User WHERE User_ID=vUserID )='F'
UNION
SELECT Access_Level AS aid,Access_Menu AS meid FROM t_Mis_Sys_RoleAccess WHERE Access_Role IN ( select Role_ID FROM t_Mis_Sys_Role WHERE (select User_IsDeleted FROM t_Mis_Sys_User WHERE User_ID=vUserID )='F' AND Role_ID IN (select Role_ID FROM t_Mis_Sys_UserRole WHERE User_ID=vUserID ) AND Role_ID IN (select Role_ID FROM t_Mis_Sys_Role WHERE Role_ID IN (select Role_id FROM t_Mis_Sys_UserRole WHERE User_ID=vUserID ) AND Role_IsDeleted='F'))
UNION
SELECT Com_LevelID AS aid,Com_MenuID AS meid FROM t_Mis_SysCommision WHERE Com_GetID=vUserID AND (select User_IsDeleted FROM t_Mis_Sys_User WHERE User_ID=vUserID )='F' AND now()>Com_StartTime AND now()<Com_EndTime
) AS temp INNER JOIN t_Mis_Sys_Menu ON meid=Menu_id;
END//