Posted by jhofmeyr on 11/22/07 11:56

On Nov 22, 1:34 am, Ed Murphy <emurph...@socal.rr.com> wrote:
> steve wrote:
> > In t-sql this should be perfectly clear:
> > DECLARE @X INT
> > SET @X=5
>
> > The variable @X can only take one value at any specific time.
> > In a relational system a procedure that returns some value at runtime
> > must behave exactly like @X.
>
> In general, this is false. For instance, you can't do
>
> SET @MyProcedure = (@X, @Y)
>
> However, you seem to merely be advocating that a procedure should
> return exactly one value (which may be a table). Upgrading all the
> existing procedures that violate this would be a major task, but
> allowing it as an option for new procedures would be reasonable
> (if it could be done reasonably efficiently).
>
> > Add to this the idea of type where each result is a different type
>
> In other words, TABLE (X INT, Y INT) is a different variable type
> from TABLE (M VARCHAR(15), N VARCHAR(15))?
>
>
>
>
>
> > This sql sp:
> > CREATE PROCEDURE SqlTwo
> > @A INT OUTPUT,
> > @B INT OUTPUT
> > AS
> > SET @A=5
> > SET @B=10
>
> > DECLARE @C INT,@D INT
> > EXEC SqlTwo @C OUTPUT,@D OUTPUT
> > SELECT @C
> > SELECT @D
>
> > makes no sense relationally because, again, there are multiple
> > results. Now there are two scalar types (int) returned instead of sql
> > 'resultsets'. Relationally there is no such thing as more than 1 thing
> > (think a variable of a type) at a time. Two scalar results are
> > realized as a 'row' type relationally, ie. 'one' thing.
> > create operator D4Two():row(A:Integer,B:Integer)
> > begin
> > result:=row(5 A,10 B);
> > end;
>
> > In this case at runtime D4Two is a variable of type row with 2 scalar
> > columns.
>
> What if the data you want to return is not multiple scalars, but
> rather multiple tables? Upon reflection, I suppose tables could
> be nested in this model, i.e. you can return
> TABLE(T1 TABLE(X INT, Y INT), T2 TABLE(M VARCHAR(15), N VARCHAR(15)))
>
> This would allow bad developers to commit the common 'a,b,c' 1NF
> violation in a whole new way, but then bad developers can screw
> things up in any language.
>
> > From the relational perspective a table/row/list is a variable that
> > behaves exactly like a variable in a programming language. Its value
> > can be assigned to other values just like a t-sql integer variable
> > can.
> > It can be compared to other variables (for equality) just like a t-sql
> > integer variable. It can be passed as an argument to a procedure just
> > like a t-sql integer variable. For these reasons why MS decided to
> > call
> > something a 'table variable' remains a mystery.
>
> But you agree that (1) it has some features of variables, and (2) it
> could reasonably be extended to have more features of variables?
>
> > Sql distinguishes between user defined functions and procedures. But
> > sql user defined functions are on the same exact level of procedures
> > when looked at from the point of view of 'variables'. Neither one
> > has anything to do with the idea of a relational variable. All this
> > artificial distinction does is serve to make it harder for users to
> > understand the relational model :) (Why sql choose to create a user
> > define function/procedure dichotomy is another topic. But think of
>
> > 'where' and 'having').
>
> I'd guess these are both for efficiency. They enforce some useful
> clarity, too (I also prefer FROM X JOIN Y ON X.Z = Y.Z over
> FROM X, Y WHERE X.Z = Y.Z because table joins are a distinct
> concept that's worth keeping separate.)
>
> > Rather than center on particular synatax or pseudo-syntax I think it
> > is
> > the ideas that the relational model is based on that is important.
> > And what we're talking about here is just a slice of the relational
> > model. The relational model is not rocket science:) It's actually
> > quiet straightforward. Ironically it's sql that is out in left field.
> > The relational model is in line with all current programming
> > languages.
> > Unfortuneatly thats never been the case with sql:) This is one of the
> > reasons I find LINQ so unnecessary. Once you get the idea that a big
> > part of the relational model is all about the basic concepts of
> > variables and types I think (I at least hope) that what I've been
> > trying to explain will make perfect sense:)
>
> A lot of people find SQL pretty straightforward, especially in this
> newsgroup. Your choice of (pseudo-)syntax will make a difference to
> them. (You might get different responses from a newsgroup focusing
> on front-end programming languages, especially if they already
> resemble Pascal as D4 seems to do.)- Hide quoted text -
>
> - Show quoted text -

I feel like I'm watching a Greek person and an Italian person
discussing the virtues of speaking French :-/

[Back to original message]