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!
