How to call one stored procedure from another and use the selected records returned

There are times when you need to Call a Stored Procedure from Another and use the results in the calling procedure rather than simply returning the record set straight away. If you call one procedure from another, it'll return the records to the caller of the outer procedure, which is not what you probably want. For our example, we'll use the following table.

Create table People ( Id int identity(1,1), Name varchar(200) )
    insert into People values ('Adele')
    insert into People values ('Alan')
    insert into People values ('Charlotte')
    insert into People values ('Chris')
go

It's only a simple example, but it's enough to give you an idea. Create the Inner procedure that the outer one will call.

Create or Alter Procedure sp_GetNames as

    select Name from People
go

Now to create the outer procedure. The key here is to create a temporary table in the outer procedure that will store results from the inner procedure. Then, in the outer procedure, you process the records in the temporary table. You need to ensure that you use the Exec keyword, or it will not work.

Create or Alter Procedure sp_GetNameStartingWithC as

    create table #tmp(Name varchar(200))
    Insert into #tmp
    exec sp_GetNames

    Select * from #tmp where Name like 'C%'

go

exec sp_GetNameStartingWithC

Drop table people

The only other way is to create a permanent table. In the inner procedure, you store the results of the inner procedure that you want to be processed by the outer, and then in the outer, you access the records. This solution would be complicated, and you'd need a way to identify the records in the permanent table used in the outer procedure if the solution works in a multi-user environment.

Tags - SQL

Last Modified : June 2023