"Could Not Find Stored Procedure" in SQL Server 2008

Question

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

Msg 2812, Level 16, State 62, Procedure CASCADE_BRANCH_COUNT2, Line 19
Could not find stored procedure '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.

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

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.

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;

Answer 1

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

...
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:

...
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;
...