TSQL max value from stored procedure results most efficient way

Question

I need to determine the max value of an object based on the values of sub objects.

exec GetSubObjects @ID = ID

yields a result set of sub objects with an int for status

I need the highest status from that result set.

is there a way to do an aggregate on the results set straight away?

What is the return type of GetSubObjects? If it is a table you can use it in a from clause. — Vulcronos, Jul 24 '14 at 17:03

score 1 · Answer 1 · answered Jul 24 '14 at 17:06

1

You could use TABLE variable and insert results there

DECLARE @T TABLE(
    COL1 INT
)

INSERT INTO @t
exec GetSubObjects @ID = ID

then you have results in table so you can do anything you like

SELECT MAX(COL1) FROM @T

answered Jul 24 '14 at 17:06

that's pretty much what I got to. I did some research on not having to define the table var/temp table, seems like mixed reviews on using OPENROWSET (http://stackoverflow.com/questions/653714/how-to-select-into-temp-table-from-stored-procedure). – AZ Chad Jul 24 '14 at 17:37
I now have a problem in that what I'm ultimately trying to get is a result set of the outer objects with the status of each object based that select max. A function makes sense, but it seems you can't run the exec inside a function... – AZ Chad Jul 24 '14 at 17:39

1 Answers1