sql - 无法在存储过程中调用存储过程

Question

我有三个存储过程A，B，C

A的定义就像

StoredProcedure A
As 
Begin

--Some Stuff

Exec DBO.B [Derived Conitions]
Exec DBO.C [Derived Conitions]

END

但是每当我尝试执行存储过程 A 时，它都会在解析时发出警告；

The module 'A' depends on the missing object 'B'. The module will still be created; however, it cannot run successfully until the object exists.
The module 'A' depends on the missing object 'C'. The module will still be created; however, it cannot run successfully until the object exists.

在执行时抛出异常

Could not find stored procedure 'dbo.B'.
Could not find stored procedure 'dbo.C'.

我找到了很多使用存储过程调用存储过程的答案，但没有一个对我有用。

Answer 1

您当然可以从单个 SP 中执行多个过程。您甚至可以将 1 个 SP 的结果用作另一个 SP 的参数。

在您的特定情况下，我怀疑存在权限/安全或整理错误阻止您访问 B和 C存储过程。

下面是一个 SP 链在工作中的例子。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[DerivedProcedures]
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Temporary table used to store results from SP1
    DECLARE @Results_ForStoredProcedure1 TABLE 
    (
        [SPID] INT, 
        [Status] NVARCHAR(50), 
        [Login] NVARCHAR(50), 
        [HostName] NVARCHAR(50), 
        [BlkBy] NVARCHAR(5), 
        [DBName] NVARCHAR(50),
        [Commad]  NVARCHAR(50),
        [CPUTime] INT, 
        [DiskIO] INT,
        [LastBatch] NVARCHAR(50),
        [ProgramName] NVARCHAR(50),
        [SPID2] INT,
        [RequestId] INT
    )

    -- Execute SP1
    INSERT INTO @Results_ForStoredProcedure1 
    EXEC sp_who2

    -- Temporary table to store the results from SP2
    DECLARE @Results_ForStoredProcedure2 TABLE 
    (
        [DatabaseName] NVARCHAR(50),
        [DatabaseSize] INT,
        [Remarks] NVARCHAR(50)
    )

    -- Execute SP2
    INSERT INTO @Results_ForStoredProcedure2
    EXEC sp_databases 

    -- do something with both SP results
    SELECT DISTINCT SP2.* 
    FROM @Results_ForStoredProcedure1 AS SP1
        INNER JOIN @Results_ForStoredProcedure2 AS SP2 ON SP2.DatabaseName = SP1.DBName
    WHERE SP1.DBName IS NOT NULL



END
GO

-- TEST
EXECUTE [dbo].[DerivedProcedures]