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.
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:
@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.|
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