[Poop-group] RFC: scope of database routines / stored procedures

[Darren D. <darren@DarrenDuncan.net>]

I am thinking of making a significant design change to my 
SQL::SyntaxModel / Rosetta modules within the next week or so, but I 
am debating whether it would be an improvement or a step backwards. 
This change would be mostly to data, rather than code, because the 
modules are heavily data-driven.

So far I have made an explicit limitation on what SQL routines 
defined by SQL::SyntaxModel can do, such that they can mainly do 
queries and DML, but can not make any schema changes.

To my understanding, this is consistent with what a lot of database 
products actually support; to alter a database schema, you issue the 
DDL one at a time from a SQL-client; you can not create, for example, 
a stored procedure schema object which will create or alter other 
schema objects when executed.

On the other hand, my reading of the SQL-2003 standard says simply 
that SQL routines are simply a named list of ordered SQL statements, 
where each SQL statement is the same as one that can be submitted for 
execution independently by a SQL-client.

So my question for you is whether it would be practical for me to 
support DDL SQL inside of SQL-routine definitions?

As a particularly pathological case, would it make sense for one 
SQL-routine to declare another SQL-routine when executed, wherein 
that declared routine may contain variable names or argument names 
that are the same as the creating routine?

How many and which database products would actually support this sort of thing?

And I'm not talking about "dynamic SQL" here (which I don't want to 
get into), but rather static SQL.  Kind of like the difference 
between "eval block" and "eval string"; I only want to do the former, 
where the inner code is compiled and checked for correctness at the 
same time as the outer code.

OTOH, if the only way to do DDL in a routine is with dynamic SQL, 
then I would like to know.

Thank you in advance. -- Darren Duncan