Posted by Ed Murphy on 11/22/07 01:34

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.)

[Back to original message]