SQL Server SP optimal way to get multiple cursors with related data

Question

What is the best technique for getting related records by SP using one db round-trip. I need to create Master.List structure on app level in C#.

I have master-detail tables: 1. I need to search rows in detail table. 2. I need to find corresponding rows in master table. 3. I need to return two cursors: A: All corresponding rows from master table. B: For each row from master table all its records from detail table.

I can do it by using in-memory table (not too much records), is it fine?

DECLARE @MasterIds TABLE (Id uniqueidentifier)

INSERT INTO @MasterIds (Id)
  SELECT DISTINCT [MasterId]
    FROM [Details]
    WHERE [ColumnA] = 'Α'

SELECT *
  FROM [Master]
  WHERE [Id] IN (SELECT * FROM @MasterIds)

SELECT *
  FROM [Detail] D
  JOIN @MasterIds M
  ON D.Id = M.Id

Answer 1

Assuming you have SQL2000 or higher, I would use XML to do this:

SELECT *
FROM [Master]
LEFT OUTER JOIN [Detail]
ON [Detail].Id = [Master].Id
WHERE [Master].[Id] IN (SELECT [MasterId] FROM [Details] WHERE [ColumnA] = 'Α')
FOR XML AUTO

Answer 2

I regularly return multiple recordsets in one stored proc call

In your case, you can do this. table variables are useful but don't scale well.

SELECT * FROM [Master] M WHERE EXISTS (
        SELECT * FROM [Details] D WHERE D.[ColumnA] = 'Α' AND D.MasterId = m.iD)

SELECT * FROM [Detail] D WHERE D.[ColumnA] = 'Α'