RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Bring Array Mapping Capabilities to SQL : Page 2

Simplify your SQL tasks by giving your queries and stored procedures the ability to iterate over arrays of table names or values.

Example: A Basic Mapping in SQL
Consider the components shown in Figure 2.

Figure 2. Array Mapping in SQL: First, you map an array of input values to an array of output statements using a template, and then execute the output array.
  • The input is a list of table names. This list may be either constant or dynamic.
  • The mapping function in this case is a SELECT template, which is nothing more than a standard SELECT statement instrumented with placeholders into which the mapper inserts elements from the input list.
  • The output is a collection of DML statements, with the placeholders replaced by input values.
  • For database processing, it is natural to go beyond generating the mapping output and actually execute the statements.
Before reading the rest of this article, you might want to download the code (which includes the SP_map code), and install it. See the sidebar "Installing SP_map" for more information.

Figure 3 shows the actual execution of this procedure against Microsoft's standard AdventureWorks database, but for your convenience, here's a text representation that you can experiment with:

   EXEC SP_map
       @StmtTemplate = 'select count(*) as Rows from <MAIN_ARG>',
       @MainArray = 'Person.Address, Person.Contact, 
          Production.Culture, Sales.Customer', @Verbose = 1
Adding the @Verbose flag causes the procedure to also emit these generated statements to the Messages tab:

   select count(*) as Rows from Person.Address
   select count(*) as Rows from Person.Contact
   select count(*) as Rows from Production.Culture
   select count(*) as Rows from Sales.Customer
Author's Note: If you do not have AdventureWorks, you can substitute some table names from any available database.

Figure 3. SQL Mapping in Practice: SP_map maps each table name in the list to the template SELECT statement, yielding the number of rows, and renders each result in a separate result set.
@StmtTemplate is a regular DML statement except for its use of the token <MAIN_ARG>, which acts as the placeholder for each element of your input list.

SQL, as you know, does not include the concept of arrays or lists. So SP_map lets you emulate an array by providing a single string argument composed of a comma-separated list of elements. It's a "constant list" because it's a hard-coded constant just as in any programming language.

For this example, the display of generated code is rather trivial, but subsequent examples show that the generated code may get significantly more complicated. The diagnostic output added by the @Verbose command is useful for seeing the nitty-gritty details of what automation code is truly doing. You may even use it as a "pre-flight check" by turning off the @Process flag, which then emits the generated statements but does not execute them.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date