|
|
Posted by steve on 11/20/07 04:26
On Nov 18, 10:10 pm, Ed Murphy <emurph...@socal.rr.com> wrote:
> .
> My objection is not so much to your general idea of variables of
> type table-with-given-columns (I've recently worked with some systems
> that could be cleaner if such a thing were available; currently they
> work around it using temp tables); more to your specific use of D4 in
> all your examples, as opposed to a pseudo-code extension of SQL.
Ok, let me directly address you dislike of D4 and your preference for
a pseudo-code extension of SQL. I'll refer to your pseudo-syntax in
the thread:
comp.databases.ms-sqlserver
'Basic Anatomy of Sql Server'
http://tinyurl.com/2olako
>Quote
Then you might want to write examples in a pseudo-syntax that
/looks/ like SQL. I know this is a matter of taste, but your
examples look ugly to me. Consider:
-- Your example of a stored procedure that returns a result set, the
-- format of which can only be deduced by reading through the code.
CREATE PROCEDURE dbo.GroupByShipCountry
@Employee Integer
AS
SELECT ShipCountry,Count(*) Cnt,Min(Freight) MinFrt,Max(Freight)
MaxFrt
FROM Orders
WHERE EmployeeID=@Employee
GROUP BY ShipCountry
-- Your example of the same stored procedure rewritten in D4.
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;
-- My example of the same stored procedure rewritten in a
-- pseudo-extension of T-SQL.
CREATE PROCEDURE dbo.GroupByShipCountry
@Employee Integer,
@ResultSet Table (
ShipCountry varchar(15),
Cnt int,
MinFrt money,
MaxFrt money
) output
AS
SELECT ShipCountry,
Count(*) Cnt,
Min(Freight) MinFrt,
Max(Freight) MaxFrt
INTO @ResultSet
FROM Orders
WHERE EmployeeID=@Employee
GROUP BY ShipCountry
>Unquote
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). Syntatically
an sql tabled value function is closer in spirit to the D4 procedure
with the big difference that the name of the table valued function is
'not' a typed variable like in D4. Finally, the sql sp makes the
distinction between identifiers as variables and non-variables using
the '@'. In D4 there is no such distinction as 'all' identitifiers
are by definition variables and the '@' is superfluous. 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'.
Note that LINQ realizes an sql stored procedure exactly as sql intends
it and nothing like the relational D4. The 'functional' part of
integrated query is simply how the sp is accessed within a net
language. There is no concept of a typed variable with the name of the
sp. In other words, the OR map is mapping to the same sql as if the
mapping didn't exist. MS has added a 'functional language' within net
when what it should have done is added a functional language to the
database itself! :) D4, after all, represents the functional language
of a relational system and how easy it is to use such a language
within a present day (net) programming language. The D4 answer to
overcoming the object-relational mismatch is of a totally different
nature to the one offered thru LINQ. My objection to LINQ lies in the
idea that no one at MS seems to have considered an alternative.
www.beyondsql.blogspot.com
[Back to original message]
|