This question might not have any good (LLBLGen) answer but I'd like to see if anyone has any thoughts to offer.

I have several ASP.NET applications that have some reporting features where users can query based on preset options and get the result in one of several different formats (onscreen, Excel, PDF, etc). I am currently using stored procedures (and a fair amount of brute force) to do this but would prefer to use something else that might be more efficient or flexible in terms of dynamic SQL.

I've looked at resources like http://www.sommarskog.se/dynamic_sql.html for "best practices" on the T-SQL side but I don't have a good handle on what people might be doing on the DAL side.

The Excel option often returns multiple resultsets based on the parameters. In many cases, a detail resultset and a summary resultset. Using this (really) simplified example from Northwind, I would get a list of orders that meet specified criteria and also a (distinct) list of the customers who placed those orders.

--helper view
CREATE VIEW dbo.vw_OrderTotal
AS

SELECT  
    od.OrderID
    , Sum(od.UnitPrice * od.Quantity *  (1 - od.Discount)) As OrderTotal
    
FROM 
    dbo.[Order Details] od

GROUP BY 
    od. OrderID


--a stored procedure with lots of optional parameters
-- NULL in the country field will fail in this example

CREATE PROCEDURE dbo.pr_SalesReport
    (
     @StartDate datetime = NULL
    ,@EndDate datetime = NULL
    ,@MinTotal money = NULL
    ,@MaxTotal money = NULL
    ,@Country nvarchar(15) = NULL
--more parameters as desired...
    )
AS

--handle non-character null parameters by using boundary values
IF @StartDate Is Null 
BEGIN
    SELECT @StartDate = '17530101'
    
END
IF @EndDate Is Null 
BEGIN
    SELECT @EndDate = '99991231'
END


IF @MinTotal is NULL
BEGIN
    SET @MinTotal = 0
END

IF @MaxTotal is NULL
BEGIN
    SET @MaxTotal = 922337203685477 --10,000,000,000,000
                -- 922337203685477 (922,337,203,685,477.5808) is the max
END


--a list of orders
SELECT 
    o.OrderID
    ,c.CompanyName
    ,c.Region
    ,c.Country
    ,o.OrderDate
    ,o.ShippedDate
    ,ot.OrderTotal
    
FROM 
    dbo.Customers c 
    INNER JOIN Orders o
    on c.CustomerID = o.CustomerID
    INNER JOIN dbo.vw_OrderTotal ot
    ON o.OrderID = ot.OrderID
WHERE
    o.OrderDate BETWEEN @StartDate AND @EndDate
    AND ot.OrderTotal BETWEEN @MinTotal AND @MaxTotal
    AND c.Country = COALESCE(@Country, c.Country)
    
ORDER BY
    ot.OrderTotal
    ,c.CompanyName
    
-- a list of customers for those orders 
SELECT 
    c.CustomerID
    , c.CompanyName
    , c.ContactName
    , c.ContactTitle
    , c.Address
    , c.City
    , c.Region
    , c.PostalCode
    , c.Country
    , c.Phone
    , c.Fax
    
from dbo.Customers c

WHERE 
    AND c.Country = COALESCE(@Country, c.Country)
    AND c.CustomerID IN
    (
    SELECT o.CustomerID
    FROM 
    Orders o
    INNER JOIN dbo.vw_OrderTotal ot
    ON o.OrderID = ot.OrderID
    WHERE
o.OrderDate BETWEEN @StartDate AND @EndDate
        AND ot.OrderTotal BETWEEN @MinTotal AND @MaxTotal   
    
    )
    
ORDER BY
    c.CompanyName


-- comment out any parameter you like
EXEC pr_SalesReport
     @StartDate = '1996-01-01'
    ,@EndDate = '1996-12-31'
    ,MinTotal = 100
    ,@MaxTotal = 500
    ,@Country = 'USA'

The basic idea would be to return one collection and then also return (extract?) related collection(s) using the same criteria as the first one.

Joel Reinford Data Management Solutions LLC

Thats what prefetch paths are doing: you specify the filter for the orders, and a prefetch path for the customers as well (order -> customer), and the customers are then fetched using the filter on the orders, eventually with a shortcut with id's.