'Is his own manager'过程错误

Question

我需要编写一个过程来检查在数据出错后，员工是否出现在其经理中。我创建了该过程，但我有一个与 nr 相关的错误（关键字 IF 附近的语法不正确）。为什么会出现它以及如何纠正它。我是使用SQL Server的初学者，所以欢迎任何帮助。

这是过程：

CREATE PROCEDURE IsManager( @Id INT)
 as
    WITH Managers
    AS (
        SELECT id, id_manager 1 AS LEVELC
        FROM [DBO].[employees]
        WHERE id = @Id
        UNION ALL
        SELECT P.id, P.id_manager H.LEVELC+1
        FROM  [DBO].[employees] E
        inner join Ancestors H
            ON E.id=H.id_manager
   )    
    SELECT COUNT(*) AS nr FROM (SELECT * FROM Managers where Managers.id=@Id
    IF nr=0
    PRINT 'Error '
    ELSE
    PRINT 'Your database has no errors'

Answer 1

试试看

CREATE PROCEDURE IsManager( @Id INT)
 as
    declare @nr int
    WITH Managers(id, id_manager, levelc)
    AS (
        SELECT id, id_manager, 1 AS LEVELC
        FROM [DBO].[employees]
        WHERE id = @Id
        UNION ALL
        SELECT P.id, P.id_manager, H.LEVELC+1
        FROM  [DBO].[employees] E
        inner join Ancestors H
            ON E.id=H.id_manager
   )    
  SELECT @nr = COUNT(*) FROM Managers where Managers.id=@Id
  IF @nr=0
    PRINT 'Error '
  ELSE
    PRINT 'Your database has no errors'