You can use table valued functions in SQL Server. Other than that I can not think of a way to use result from one select in another select in SP except for using temp tables or table variables or CTE.

If it's SQL Server 2005 then look at common table expressions (CTE).

>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