请问在sqlserver的存储过程中怎样使用if-else if -else if-else

[color_tree]

请问各位我在sqlserver的存储过程中使用if()--else if()-else()抱错



CREATE PROCEDURE prc_queryMappingData
@BadgeNumber varchar(10),
@DeptId INT,
@UserType varchar(30),
@Group_id INT,
@SubDeptId INT,
@AuditFlag char(10)

AS



IF @DeptId IS NULL
BEGIN
IF @AuditFlag IS NOT NULL --if (data.getAuditFlag()==true)
BEGIN
IF @UserType='USER_DEPT_ADMIN'

SELECT c.EID, c.BADGE_NO, c.NAME_CHN, d.group_id FROM dbo.EMPLOYEE c INNER JOIN (SELECT eid, group_id FROM att_group_log a WHERE a.effect_date = (SELECT MAX(effect_date) FROM att_group_log b WHERE a.eid = b.eid) AND (a.group_id IN (@Group_id )))

ELSE IF @UserType='USER_SUB_DEPT_ADMIN' --else if(user.getUserType()==ConstVariable.USER_SUB_DEPT_ADMIN)

SELECT c.EID, c.BADGE_NO, c.NAME_CHN, d.group_id FROM dbo.EMPLOYEE c INNER JOIN (SELECT eid, group_id FROM att_group_log a WHERE a.effect_date = (SELECT MAX(effect_date) FROM att_group_log b WHERE a.eid = b.eid) AND (a.group_id IN (@SubDeptId )))


ELSE IF @UserType='USER_GROUP_ADMIN' --else if(user.getUserType()==ConstVariable.USER_GROUP_ADMIN)

SELECT c.EID, c.BADGE_NO, c.NAME_CHN, d.group_id FROM dbo.EMPLOYEE c INNER JOIN (SELECT eid, group_id FROM att_group_log a WHERE a.effect_date = (SELECT MAX(effect_date) FROM att_group_log b WHERE a.eid = b.eid) AND (a.group_id IN (@DeptId )))

END
END


ELSE IF @SubDeptId IS NULL --如果没有二级部门的限制

SELECT c.EID, c.BADGE_NO, c.NAME_CHN, d.group_id FROM dbo.EMPLOYEE c INNER JOIN (SELECT eid, group_id FROM att_group_log a WHERE a.effect_date = (SELECT MAX(effect_date) FROM att_group_log b WHERE a.eid = b.eid) AND (a.group_id IN (@Group_id )))

ELSE IF @Group_id IS NULL --如果没有三级部门的限制

BEGIN
IF @AuditFlag IS NOT NULL --if(data.getAuditFlag()==true)
BEGIN
IF @UserType='USER_SUB_DEPT_ADMIN' --if(user.getUserType()==ConstVariable.USER_SUB_DEPT_ADMIN)

SELECT c.EID, c.BADGE_NO, c.NAME_CHN, d.group_id FROM dbo.EMPLOYEE c INNER JOIN (SELECT eid, group_id FROM att_group_log a WHERE a.effect_date = (SELECT MAX(effect_date) FROM att_group_log b WHERE a.eid = b.eid) AND (a.group_id IN (@Group_id )))

ELSE IF @UserType='USER_DEPT_ADMIN' --else if(user.getUserType()==ConstVariable.USER_DEPT_ADMIN)

SELECT c.EID, c.BADGE_NO, c.NAME_CHN, d.group_id FROM dbo.EMPLOYEE c INNER JOIN (SELECT eid, group_id FROM att_group_log a WHERE a.effect_date = (SELECT MAX(effect_date) FROM att_group_log b WHERE a.eid = b.eid) AND (a.group_id IN (@SubDeptId )))

ELSE IF @UserType='USER_GROUP_ADMIN' --else if(user.getUserType()==ConstVariable.USER_GROUP_ADMIN)

SELECT c.EID, c.BADGE_NO, c.NAME_CHN, d.group_id FROM dbo.EMPLOYEE c INNER JOIN (SELECT eid, group_id FROM att_group_log a WHERE a.effect_date = (SELECT MAX(effect_date) FROM att_group_log b WHERE a.eid = b.eid) AND (a.group_id IN (@DeptId )))



END
ELSE

SELECT c.EID, c.BADGE_NO, c.NAME_CHN, d.group_id FROM dbo.EMPLOYEE c INNER JOIN (SELECT eid, group_id FROM att_group_log a WHERE a.effect_date = (SELECT MAX(effect_date) FROM att_group_log b WHERE a.eid = b.eid) AND (a.group_id IN (@SubDeptId )))

END

ELSE --//如果指定三级部门

SELECT c.EID, c.BADGE_NO, c.NAME_CHN, d.group_id FROM dbo.EMPLOYEE c INNER JOIN (SELECT eid, group_id FROM att_group_log a WHERE a.effect_date = (SELECT MAX(effect_date) FROM att_group_log b WHERE a.eid = b.eid) AND (a.group_id IN (@Group_id )))


IF @BadgeNumber IS NOT NULL --对于员工号限制 if(data.getBadgeNumber()!=null&&!data.getBadgeNumber().trim().equals(""))

SELECT c.EID, c.BADGE_NO, c.NAME_CHN, d.group_id
FROM dbo.EMPLOYEE c INNER JOIN (SELECT eid, group_id FROM att_group_log a
WHERE a.effect_date = (SELECT MAX(effect_date) FROM att_group_log b
WHERE a.eid = b.eid) AND (a.group_id IN (@Group_id ))) d ON c.EID = d.eid WHERE (c.BADGE_NO LIKE @BadgeNumber) ORDER BY c.EID




GO












Server: Msg 156, Level 15, State 1, Procedure prc_queryMappingData, Line 24
Incorrect syntax near the keyword 'ELSE'.
Server: Msg 156, Level 15, State 1, Procedure prc_queryMappingData, Line 29
Incorrect syntax near the keyword 'ELSE'.
Server: Msg 156, Level 15, State 1, Procedure prc_queryMappingData, Line 33
Incorrect syntax near the keyword 'END'.
Server: Msg 156, Level 15, State 1, Procedure prc_queryMappingData, Line 41
Incorrect syntax near the keyword 'ELSE'.
Server: Msg 156, Level 15, State 1, Procedure prc_queryMappingData, Line 50
Incorrect syntax near the keyword 'ELSE'.
Server: Msg 156, Level 15, State 1, Procedure prc_queryMappingData, Line 54
Incorrect syntax near the keyword 'ELSE'.
Server: Msg 156, Level 15, State 1, Procedure prc_queryMappingData, Line 60
Incorrect syntax near the keyword 'END'.
Server: Msg 156, Level 15, State 1, Procedure prc_queryMappingData, Line 65
Incorrect syntax near the keyword 'END'.
Server: Msg 156, Level 15, State 1, Procedure prc_queryMappingData, Line 72
Incorrect syntax near the keyword 'IF'

[jacobsan]

IF()
BEGIN

END
ELSE
BEGIN

END

[jacobsan]

每个程序体部分都要用begin end括住，而不是整个

[marco08]

if()
begin
...
end
else if()
begin
...
end
else
begin
...
end

[jacobsan]

ls的貌似很活跃。。。

[eform01]

必须要加begin---end吗？？

[jackeyabc]

楼上的楼上貌似新来的~