Using dataset from stored proc in another proc

There are times when it is really convenient to gather data in a view and then use that view in one or more stored procs to build a complex dataset.  Sometimes, however, that base dataset can run much more efficiently if you use one or more temp tables to join data.  Views can;t handle this type of logic, but stored procs can.  Historically, it was difficult to use the dataset returned rom a stored proc in the logic of other stored procs, but nowadays it is relatively easy using temp table variables.

Say we have a stored proc that returns a dataset like this:


create proc myBaseData(
 @param1 int,
 @param2 datetime
)
as
begin
 select @param1 as int1,
   @param2 as date1
end
go


Now say we want to call that proc and use the dataset in another proc, but we don;t want to save the records to a physical table.  We can insert the proc results in a table variable like so:

create proc MyCallingProc(
 @param1 int
)
as
begin
 declare @mytable as table(
  col1 int,
  col2 datetime
 )

 declare @d datetime
 select @d=convert(varchar,GETDATE(),112)

 insert into @mytable
 exec myBaseData @param1,@d

 select * from @mytable
end
go


We can then execute it as follows:

exec MyCallingProc 1

The big bonus here is that I don;t have to create a view and take the performance hit of filtering it every time I join it.  The params on the proc can be used to filter the results before joining.

Another bonus is that I can set a primary key (unique index) on the table variable, which is something I can't do in the view.

Did this help you?  If so, please leave a comment!