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

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.


Bring Array Mapping Capabilities to SQL : Page 7

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




Application Security Testing: An Integral Part of DevOps

The SP_map Stored Procedure API
Now that you've seen what SP_map can do, here's a quick reference guide to the parameter syntax; the full SP_map API is available on my open source website:

SP_map @StmtTemplate, @MainArray, @AuxArray, @Permute, @Verbose, @Process


@StmtTemplate ::= <DML statement> @MainArray ::= "'" { <main item> "," } <main item> "'" | <select statement> @AuxArray ::= "'" { <aux item> "," } <aux item> "'" | <select statement> @Permute ::= "1" to perform all permutations, or "0" to match indices @Accumulate ::= "1" to collect output into one result set @AccumulateFilter ::= <filtering predicate> @Verbose ::= "0"-"3" for no diagnostic output or progressively more @Process ::= "0" to only generate statements, "1" to actually process them

Table 1 provides the details for each parameter.

Table 1. SP_map Stored Procedure Parameters: Here's a complete list of the SP_map procedure's parameters, showing the datatype, default value, and a brief description of each.
Parameter Datatype Default Description
@StmtTemplate nvarchar 2048 none A DML statement (Select, INSERT, DBCC, etc.) that you wish to map to a set of tables or other values. @StmtTemplate should contain the literals <MAIN_ARG> and <AUX_ARG> as placeholders for @MainArray or @AuxArray elements, respectively.
@MainArray nvarchar 2048 none @MainArray may be either a comma-separated list of constants wrapped as a single string, or a SELECT statement that returns a single-column result. In either case, an individual item may contain up to 128 characters.
@AuxArray nvarchar 2048 null @AuxArray has the same format as @MainArray: it may be either a comma-separated list of constants wrapped in a string, or a SELECT; statement that returns a single-column result. If @AuxArray is null, the <AUX_ARG> placeholder should not be present in the @StmtTemplate.
@Permute bit 0 If turned on, then all combinations of <MAIN_ARG> and <AUX_ARG> are evaluated. If turned off, then @MainArray and @AuxArray are referenced as pair-wise lists and must therefore have the same cardinality.
@Accumulate bit 0 For queries that return results, SP_map by default returns a separate result set for each element. Setting the @Accumulate flag instead aggregates the results into a single result set.
@AccumulateFilter nvarchar 2048 null When you accumulate result sets into a single result set, that result set is stored in a temporary table. The generated sequence of code ends with a simple SELECT * FROM #temp_table. To filter the results specify a predicate in @AccumulateFilter, which changes the statement to SELECT * FROM #temp_taple WHERE (@AccumulateFilter).
@Verbose smallint 0 For diagnostic purposes, you may display the dynamic SQL generated to process your request. Each level is cumulative, producing more (rather than alternate) output.
  • 0 = No output.
  • 1 = Statements with <MAIN_ARG> replaced (and <AUX_ARG> if not permuting).
  • 2 = Templates; statement permutations with <MAIN_ARG> and <AUX_ARG> replaced.
  • 3 = Completed statement batch ready to execute.
@Process bit 1 To see the generated SQL without executing it, set @Process to 0. To be useful, however, you must also set @Verbose to a non-zero value, otherwise no output is displayed and nothing is executed!

As you have seen, SP_map provides a powerful mechanism for mapping arrays of elements onto SQL statements. The source code (see SP_map.sql in the downloadable code) may look intimidating at first glance, but you'll find it just uses straightforward SQL in interesting combinations. Studying the generated code shown in the examples in this article will help you comprehend the full code. You'll also find a list of useful pre-defined queries that you can use in the sidebar "Useful SP_map Queries."

Michael Sorens is a freelance software engineer, spreading the seeds of good design wherever possible, including through his open-source web site, teaching (University of Phoenix plus community colleges), and writing (contributed to two books plus various articles). With BS and MS degrees in computer science and engineering from Case Western Reserve University, he has worked at Fortune 500 firms and at startups, using C#, SQL, XML, XSL, Java, Perl, C, Lisp, PostScript, and others. His favorite project: designing and implementing the world's smallest word processor, where the medium was silicon, the printer "head" was a laser, and the Declaration of Independence could literally fit on the head of a pin. You can discuss this or any other article by Michael Sorens here.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



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