Can some some post me a link or reference that can tell me how to use intermediate results sets in Sql Server. All examples I see are from tables in the database.

Like in Fox where I SELECT * from table1 into cursor mycursor

then SELECT * FROM table2 INNER JOIN mycursor ....

In Sql Server 2005 do I need to create a server cursor or ??? Another good example is I want to use a rszult set from a Stored Procedure in a subsequent query in another procedure.

Like:
lcCursor = CreateMyCursor()
select * from &lcCursor........

Thanks