Join a table that is returned by a stored procedure

[GeertVerhoeven]

Hi all,

I have a problem. I have a stored procedure (up_generate_table) that returns a table (select command). From another stored procedure I should be able to make a select statement and join on the table returned by up_generate_table. Does anybody have an idea on how to do this ?

Thanks in advance !

Kind regards,

Geert Verhoeven

 

[NoCoolHandle]

Geert the answer is no and yes..

No it can't be done via a storedproc, but yes it is possible to do what you want via a UDF (UserDefinedFunction)

This allows for the same benifits as StoredProcs but has the added benifit of allowing you to treat the return like a table...

e.g.
The following Stored Proc....

create proc x
@lastname varchar(3000)
as
select * from northwind.dbo.employees where lastname =@lastname

Could be rewritten as...

Create function [blue]x1[/blue]
(@lastname varchar(3000))
returns table
as
	return (select * from northwind.dbo.employees where lastname =@lastname)

And then you could use it in a query like..

select [blue]x1[/blue].FirstName , [blue]x1[/blue].LastName, OrderID From [blue]x1[/blue]('Fuller') join northwind.dbo.orders o on [blue]x1[/blue].Employeeid = o.Employeeid

YOu will also notice that this gives you the benifit of being able to specify only certain columns in the function...

HTH


Rob