CODEWITHSUNDEEP
sqlstored-proceduressql-server-2012azure-sql-database
user3030097
user3030097

Reputation:

Unable to call stored procedure within stored procedure

I have three stored procedures A, B, C

and definition of A is like

StoredProcedure A
As 
Begin

--Some Stuff

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

END

but whenever I tried to execute the stored procedure A, at parsing time it give waring;

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.

At execution time it throws exception

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

I found so many answers for calling a stored procedure with in stored procedure, but none of them worked for me.

Upvotes: 6

Views: 12412

Answers (2)

Nikhil Munj
Nikhil Munj

Reputation: 21

Perhaps, it sounds hilarious but I was getting the mentioned issue as I was using the wrong DB name (for example-Use 'XYZ'). Actually, in my case I was transferring a SP from one environment to another but after doing so I would not change the corresponding DB name .Due to which I was getting the error as the SPs which were involved were present in different DBs in the dissimilar environment.

In nutshell,please check the DB name which should be the very first line of your SP.

For example- Use 'XYZ'.

Upvotes: 2

Kane
Kane

Reputation: 16812

You certainly can execute multiple procedures from within a single SP. You can even us the results from 1 SP as parameters in another.

In your specific case I suspect that there is a permissions / security or collation error which is stopping you from access the B and C stored procs.

Here is an example of SP chaining at work.

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]

Upvotes: 3

Related Questions