大袋鼠的天空
时间飞逝

今天上午写了一个存储过程,总是报:ELSE附近有语法错误,如下红色字体区域,ELSE与IF的区别只是WHERE里改变了一个参数,各位高手指点一下吧...
CREATE PROCEDURE [TermGetDepartmentList]
   @CompanyID char(36)='',
   @BigDepartID char(36)='',
   @DepartID char(36)=''
AS
DECLARE @T TABLE([ID] char(36),DisplayName varchar(96),[Level] int,Sort varchar(500))
DECLARE @Level int
DECLARE @DepID char(36)
SET @DepID=''
SET @Level=0
IF @CompanyID!=''
    SET @DepID=@CompanyID
    SET @Level=1
IF @BigdepartID!=''
    SET @DepID=@BigDepartID
    SET @Level=2
IF @DepartID!=''
    SET @DepID=@DepartID
    SET @Level=3
IF (@DepID='')
        INSERT INTO @T
        SELECT [ID],DisplayName,@Level,[ID] FROM [部门] WHERE ParentID = [ID]
        WHILE @@ROWCOUNT>0
        BEGIN
         SET @Level = @Level + 1
         INSERT INTO @T
         SELECT A.[ID],A.DisplayName,@Level,(B.Sort + A.[ID]) AS SORT FROM [部门] A INNER JOIN @T B ON A.ParentID = B.[ID]
         WHERE A.[ID] != A.ParentID AND B.[Level] = @Level - 1
        END

ELSE
        INSERT INTO @T
        SELECT [ID],DisplayName,@Level,[ID] FROM [部门] WHERE ParentID
=@DepID
        WHILE @@ROWCOUNT>0
        BEGIN
  SET @Level = @Level + 1
  INSERT INTO @T
  SELECT A.[ID],A.DisplayName,@Level,(B.Sort + A.[ID]) AS SORT FROM [部门] A INNER JOIN @T B ON A.ParentID = B.[ID]
  WHERE A.[ID] != A.ParentID AND B.[Level] = @Level - 1
        END

SELECT [ID],DisplayName FROM @T ORDER BY Sort
GO

posted on 2007-07-11 10:16  大袋鼠  阅读(541)  评论(2)    收藏  举报