SQL Server 2008中的“无法找到存储过程”

I have created a stored procedure in SQL Server 2008, but I am unable to execute it. The error that gets returned is:

我在SQL Server 2008中创建了一个存储过程，但我无法执行它。返回的错误是：

Msg 2812, Level 16, State 62, Procedure CASCADE_BRANCH_COUNT2, Line 19
Could not find stored procedure 'CASCADE_BRANCH_COUNT2'

消息2812，级别16，状态62，过程CASCADE_BRANCH_COUNT2，第19行无法找到存储过程'CASCADE_BRANCH_COUNT2'

Within SQL Server Management Studio, I am right clicking on the procedure within the Object Explorer window and selecting Script stored procedure As > EXECUTE To > New Query Editor Window and then executing the procedure. So the procedure definitely exists.

在SQL Server Management Studio中，我右键单击对象资源管理器窗口中的过程，然后选择脚本存储过程为>执行到>新查询编辑器窗口，然后执行该过程。所以程序肯定存在。

I've tried restarting SQL Server Management Studio, refreshing the intellisense cache and creating the procedure under a different name.

我尝试重新启动SQL Server Management Studio，刷新智能感知缓存并使用其他名称创建过程。

The procedure is recursive, so I don't know if that is maybe causing problems.

该过程是递归的，所以我不知道这是否可能导致问题。

The procedure is meant to find the top node of a tree and then traverse through every node within the tree in order to assign the branch count to the record associated with each node.

该过程旨在找到树的顶部节点，然后遍历树中的每个节点，以便将分支计数分配给与每个节点相关联的记录。

The procedure executes without any errors if the tree containing @ClientId only consists of one node. Any more than one node and it seems to produce the error message.

如果包含@ClientId的树只包含一个节点，则该过程执行时没有任何错误。任何多个节点，它似乎产生错误消息。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [MyDB].[CASCADE_BRANCH_COUNT2]
    (@ClientId INT, @UserId INT, @BranchCount INT, @Override INT)
AS
BEGIN
    DECLARE @Counter INT = 0;
    DECLARE @Children INT = 0;
    DECLARE @Buffer INT = 0;

    --Not Top Node Of Tree
    IF @Override = 0 and @ClientId in (SELECT BranchClientId FROM ClientBranches)
    BEGIN
        SET @Buffer = (SELECT TOP 1 ParentClientId 
                       FROM ClientBranches 
                       WHERE BranchClientId = @ClientId)
        EXEC CASCADE_BRANCH_COUNT2 @Buffer, @UserId, @BranchCount, 0;
    END;

    --Reached Top Node And Updating Fields For All Child Nodes
    ELSE
    BEGIN
        IF @Override = 1 OR @ClientId IN (SELECT ParentClientId 
                                          FROM ClientBranches)
        BEGIN
            SET @Children = (SELECT COUNT(BranchClientId) 
                             FROM ClientBranches 
                             WHERE ParentClientId = @ClientId);

            WHILE @Counter < @Children
            BEGIN
                SET @Buffer = (SELECT TOP 1 BranchClientId
                               FROM ClientBranches
                               WHERE ParentClientId = @ClientId  
                                 AND BranchClientId NOT IN (SELECT TOP (@Counter) BranchClientId
                                                            FROM ClientBranches
                                                            WHERE ParentClientId = @ClientId))

                EXEC CASCADE_BRANCH_COUNT2 @Buffer, @UserId, @BranchCount, 1;
                SET @Counter = @Counter + 1;
            END;

            IF @ClientId IN (SELECT ClientId FROM ClientColumns)
            BEGIN
                UPDATE ClientColumns
                SET ClientColumns.BranchCount = @BranchCount,
                    ClientColumns.UpdatedUserId = @UserId
            END;
        ELSE
        BEGIN
            INSERT INTO ClientColumns (ClientId, CreatedUserId, CreatedOn, UpdatedUserId, BranchCount)
            VALUES(@CLIENTID, @UserId, GETDATE(), @USERID, @BranchCount)
            END;
        END;
    END;
END;

1 个解决方案

#1

The problem is that you created the proc in the MyDB schema, but you are executing it in the default:

问题是您在MyDB架构中创建了proc，但是您在默认情况下执行它：

...
BEGIN
SET @Buffer = (SELECT TOP 1 ParentClientId FROM ClientBranches WHERE BranchClientId = @ClientId)
EXEC CASCADE_BRANCH_COUNT2 @Buffer, @UserId, @BranchCount, 0;
END;  ^--- Here
...

You need to specify the schema in the proc:

您需要在proc中指定架构：

...
BEGIN
SET @Buffer = (SELECT TOP 1 ParentClientId FROM ClientBranches WHERE BranchClientId = @ClientId)
EXEC MyDB.CASCADE_BRANCH_COUNT2 @Buffer, @UserId, @BranchCount, 0;
END;   ^--- Here
...

You also have another call further down in the procedure that needs to be fixed too:

您还需要在程序中进一步调用另一个需要修复的调用：

...
EXEC MyDB.CASCADE_BRANCH_COUNT2 @Buffer, @UserId, @BranchCount, 1;
SET @Counter = @Counter + 1;
...

#1