Posted by Ed Murphy on 11/20/07 09:57

steve wrote:

> To begin with, the idea of a stored procedure returning a 'result' is
> an sql concept. This concept does not exist in a relational (D4)
> system. Relationally, a stored procedure only exists when it is
> created. The execution of a sp, its runtime realization, does not
> involve the definition of the procedure nor the idea of 'returning'
> something from it. Relationally at runtime what sql see's as a
> procedure and a result 'is' a variable of the type of the result. This
> is the huge difference between the two systems. Relationally the
> '@ResultSet' and the idea of inserting a query result into it is
> contradictory and meaningless. The 'name' of the procedure 'is' the
> variable (table), there is no result from a sp (ie. sql).

You're being highly unclear again. You may have a good point
somewhere in here, but the communication barrier is so high
that I can't find it.

Your previous complaint about SQL stored procedures was that
there's no one part of them that unambiguously defines what
its result set(s) (zero/one/many) will look like; it requires
parsing the code, and may in fact vary depending on the execution
path taken during any given run. I suggested moving the conceptual
role of "share data with whoever called me" away from these untyped
result set(s), and into a strongly typed (table-typed) output
variable, to address this specific concern. Calling that variable
"@ResultSet" may have confused that issue; it is not a result set
as currently used in SQL, but rather a parameter that fills the
conceptual role currently filled by result sets.

I recently discovered that T-SQL has had TABLE variables since 2K:
http://www.odetocode.com/Articles/365.aspx
I had not had occasion to use them before, but it turns out that they
look just like my hypothetical extension. They can't be passed
between procedures, but if they could be, then would that be what
you're looking for?

> The 'output'
> declaration in the sql sp is based on the general sql idea of
> 'returning' something. Such a declaration is superfluous relationally
> as, again. there is no concept of 'returning a something' from a 'this
> sp'.

Let's look at your example again:

create operator GroupByShipCountry (Employee:Integer):
table{ShipCountry:String,Cnt:Integer,MinFrt:Money,MaxFrt:Money}
begin
result:=
Orders
where EmployeeID=Employee
group by {ShipCountry}
add{Count() Cnt,Min(Freight) MinFrt,Max(Freight) MaxFrt} ;
end;

This looks like a stored procedure that returns something (except
that, unlike a SQL stored procedure, this one states up front
what that something will look like). Is there more to it than
that? Are you trying to say something about the difference between
procedures and functions? (SQL has functions, too; procedures can
alter data, functions can't.)

[Back to original message]