Kevin,

What you can do is have your SP create the detailed result set into a temp table. From there you can query the temp table with your group by to summarize and the return both result sets... sort of like:

CREATE TABLE #detail
pk int primary key,
account int
amount numeric(12,2)

-- get detail records
INSERT #detail
SELECT (all your complicated stuff)
FROM tablename
WHERE (more complicated stuff)

-- get summarys
SELECT accounts, sum(amount) amount
FROM #detail
GROUP BY account

-- return detail
SELECT * FROM #detail


This will return two record sets. As soon as the SP ends the local temp table, #detail will be dropped.

HTH,
BOb



>I *could*, and that was an option I neglected to mention.
>
>The initial query is fairly complex (there are a dozen different conditions), so the query string still needs to be built in the middle tier and then passed to the stored proc.
>
>There's another issue (and it's probably just my lack of understanding of how one would do this in SQL)....I need to get back a detailed results set (e.g. 1000 records) and a summary results set of those 1,000 (which might be, say, 20 records). I've never been able to figure out how to bring back multiple result sets in one single query/stored proc.
>
>Only thing I've ever been able to come up with even close is a union, where I'd bring back one result set with an additonal flag that indicates whether each record is from the detailed results set or the summary set. (The summary set will usually have the same structure as the detailed set). Then the middle tier would just strip out the summary set. It seems a bit kludgy, and I'm searching for something cleaner.
>
>Thanks,
>Kevin