On 11/22/2017 02:39 PM, Corey Huinker wrote: > > > T-SQL procedures returns data or OUT variables. > > I remember, it was very frustrating > > Maybe first result can be reserved for OUT variables, others for > multi result set > > > It's been many years, but if I recall correctly, T-SQL returns a > series of result sets, with no description of the result sets to be > returned, each of which must be consumed fully before the client can > move onto the next result set. Then and only then can the output > parameters be read. Which was very frustrating because the OUT > parameters seemed like a good place to put values for things like > "result set 1 has 205 rows" and "X was false so we omitted one result > set entirely" so you couldn't, y'know easily omit entire result sets. >
Exactly. If we want to handle OUT params this way they really need to be the first resultset for just this reason. That could possibly be done by the glue code reserving a spot in the resultset list and filling it in at the end of the procedure.
I fail to understand how that can work though. Wouldn't we have to buffer all the resultset contents on the server in tuplestores or similar, so we can send the parameters and then the result sets?
Isn't that going to cause a whole different set of painful difficulties instead?
What it comes down to is that if we want to see output params before result sets, but the output params are only emitted when the proc returns, then someone has to buffer. We get to choose if it's the client or the server.