Allow storing a table in a variable

[ghost]

Currently variables can only hold single scalar values or array.

Set $val = (FROM * FROM Mytable) dosn't work. Set $val = (SELECT name FROM MyTable) only uncludes the first found value.

Make variables able to hold table-like data, for example:

Set $data = (Select name, email From Users Where status='online') As table
For Each $data As $Row
Begin
    Print printf('User: %s \tEmail: %s', $Row.name, $Row.email)
End

(I now, For Each is currently not available)

Or let variables pointing to tables, eventually temp tables. So you can create a temp table of a (Select * From Blah) Statement and let the variable pointing to this table.

[electroly]

I would like to do this. I might be able to allow something like:

DECLARE $data = SELECT * FROM whatever;

Not having parentheses would indicate that you want the whole table, whereas with parentheses it would grab the first value.

[electroly]

Although for your code example, I envision something like this being possible:

FOR EACH $row IN SELECT name, email FROM users WHERE status = 'online'
BEGIN
    PRINT PRINTF('User: %s \tEmail: %s', $row.name, $row.email)
END

I think FOR EACH should allow a SELECT query right there in the statement like that, without needing to assign to a variable. But table variables would still be useful in other situations.

[electroly]

Or perhaps...

PRINT SELECT PRINTF('User: %s \tEmail: %s', name, email) FROM users WHERE status = 'online';

which would be the same as doing

DECLARE @x = SELECT PRINTF('User: %s \tEmail: %s', name, email) FROM users WHERE status = 'online';
PRINT @x; -- @x is a table variable with one column of strings

I would just need to extend PRINT to accept a table variable.

[ghost]

My idea was to use a variable as a table, as I already wrote here #28 Datatypes like the ARRAY or something like a MAP (key,value pairs) is good for scripts, but when working with SQL and data the best multiple values type is a table (fixed, temp or virtual).

The printf thingy was just to do something with the data 😀