Simplify your SQL tasks by giving your queries and stored procedures the ability to iterate over arrays of table names or values.
by Michael Sorens
November 1, 2007
QL has been evolving—slowly—for over three decades, (see Wikipedia for a historical summary). While SQL in the hands of an expert is quite powerful, it's sometimes cumbersome compared to other programming languages. It does not scope variables the way modern, structured languages do, and lacks some basic functionality. However, extending the language with stored procedures can help. The general-purpose stored procedure described here enhances the expressive power of SQL by performing some useful array, matrix, and list-processing tasks. For example, with one line of code you can:
List the number of rows of each table in an arbitrary list of tables in a single result set.
Feed the result of an arbitrary query to a template of an INSERT statement for a different table, and then execute each of those INSERT statements.
Re-seed an arbitrary set of tables with the same seed value, or each with a different seed value.
For any group of tables, show the number of rows where a given field is above a certain threshold, even if the field name varies from table to table.
For a set of users and a set of permissions, enable each permission for each user (i.e. all permutations).
This is a powerful routine to add to any SQL library, letting you accomplish tasks in minutes that would previously have taken hours.
It's quick, easy and you get access to all the articles on DevX.
This registration/login is to allow you to read articles on devx.com. Already a member?
To become a member of DevX.com create your Member Profile by completing the form below. Membership is free!
QL has been evolving—slowly—for over three decades, (see 
