Efficient multiple selects in a single stored proc

I need to create a stored proc that returns two result sets. The second set contains records that are related to the records in the first set through a parent/child relationship.

For example, the first statement returns orders:

SELECT ID, col2, col3, ...
FROM dbo.Orders
WHERE x=y

the second statement returns individual items for these orders:

SELECT ID, OrderID, col3, col4, ...
FROM dbo.OrderItems
WHERE OrderID IN (<IDs from first statement>)

I'm looking for the most efficient way to achieve this. Currently my solution looks like this:

-- create a table variable to hold results from first statement
DECLARE @Result TABLE 
(
   ID int NOT NULL, 
   col2 nvarchar(50),
   col3 nvarchar(50)
)

-- select orders
INSERT INTO @Result (ID, col2, col3)
SELECT ID, col2, col3, ...
FROM dbo.开发者_运维百科Orders
WHERE x=y

SELECT * FROM @Result 

-- select order items corresponding to the orders in the @Result table
SELECT ID, OrderID, col3, col4
FROM dbo.OrderItems I
INNER JOIN @Result R ON R.ID = I.OrderID

This seems to work quite well. However requirements are so that the WHERE clause in the first statement gets quite complex and lends itself to using a CTE (WITH statement), but having both a WITH and a table variable gets pretty convoluted. What is a better way to solve this?

---

I think the best way here will be to use a CTE(Common Table Expression).

Syntax for the same is

;With CTE as
(
   Select ...
)

Your Query should be like this...

;WITH @Result AS
(
   SELECT 
     ID, 
     col2, 
     col3, ...
   FROM 
    dbo.Orders
   WHERE x=y
)
SELECT 
  ID, 
  OrderID, 
  col3,
  col4
FROM 
  dbo.OrderItems I
INNER JOIN
  @Result
ON
  @Result.ID   = I.OrderID
ON