今天上午写了一个存储过程,总是报: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