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.
First, you need a little background on the concept of mathematical mapping (as opposed to geographic mapping). Perl and C# work well for this because they have powerful mapping capabilities that help make the subsequent explorations into SQL more fruitful. Then you’ll see some SQL examples using the SP_map stored procedure that accompanies this article. These function as more than mere examples, however; they serve as platforms for launching into a discussion of the program’s key features and the full stored procedure API. Finally you’ll see details on installing the stored procedure in your environment.
Background
Mapping features are commonly found in functional programming languages, which are less commonly used than procedural or imperative languages. A key distinction, as stated on the Wikipedia page, is: “Imperative programs tend to emphasize the series of steps taken by a program in carrying out an action, while functional programs tend to emphasize the composition and arrangement of functions, often without specifying explicit steps.”
Perl is typically considered an imperative language but may be used in the style of a functional language as well. In particular, it provides an exceedingly useful mapping capability. A simple example is mapping an array of integers to their ASCII-equivalent characters, as illustrated in Figure 1, and that you can accomplish with any of these three one-liners:
@chars = map(chr, @nums); @chars = map { chr } @nums; @chars = map { chr($_) } @nums; |
? |
| Figure 1. Basic Array Mapping Illustration: Two equivalent views of mapping an array of inputs to an array of outputs using the Perl chr() function. |
The map operator is not limited to serving as a function conduit; it can also serve as a processing operator, for example:
%hash = map { getkey($_) => $_ } @array;In words, this one-line statement says to feed each element of the input array (@array) to the code block of the map (getkey($_) => $_) and store the results in the hashtable (%hash). That (perhaps arcane looking) map invocation is exactly equivalent to this more traditional procedural form:
%hash = (); foreach $element (@array) { $hash{getkey($element)} = $element; }Without knowing Perl, you could probably surmise that the code fragment loops through the given array and builds a hash table.
| Author’s Note: The preceding examples were taken from Perldoc’s map documentation page. |
Functional map expressions in Perl can become quite intricate, as in this example of the Schwartz sorting transform popularized in the quintessential Perl Cookbook, section 4.15, which sorts structures by a particular field contained within each record:
@ordered = map { $_->[1] } sort { $a->[0] <=> $b->[0] } map { [compute(), $_] } @unordered;Even if you’re not enthusiastic about Perl, it’s useful to show a somewhat canonical example of mapping. Now take a look at the C# equivalent. C# (version 2.0 or greater) provides the ConvertAll generic method, which is effectively the same as Perl’s map function. The article entitled Functional List Processing in C# 2.0 presents this handy example:
List outputList = list.ConvertAll(delegate(string s) { return s.ToLower(); });The preceding code defines an anonymous function as a delegate, passes that to the map method (ConvertAll) of your original List object (list), and returns a new List with all members converted to lowercase. For reference, the Perl equivalent is:
@outputList = map { tr/A-Z/a-z/;$_} @list;If you attempt to use the C# version given above, you will likely get the error “The type arguments for method ConvertAll cannot be inferred from the usage. Try specifying the type arguments explicitly.” Seems reasonably clear and helpful, but figuring out syntactically how to correct it is far from obvious. Here is the revised version with an explicit type added:
List outputList = list.ConvertAll(delegate(string s) { return s.ToLower(); });And just as Perl can produce complex transformations by chaining, so can C#. This example from the same article generates a list of all files larger than a given size:
List paths = new List( Directory.GetFiles(directory)); return paths.ConvertAll( File.OpenRead ) .FindAll( delegate(FileStream f) { return f.Length >= bigLength; } ) .ConvertAll( delegate(FileStream f) { return f.Name; } );That gives you the flavor of mapping in the domain of conventional programming; the next section introduces you to some examples showing how you can use mapping in real-world database programming.
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 ', @MainArray = 'Person.Address, Person.Contact, Production.Culture, Sales.Customer', @Verbose = 1Adding 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 , 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.
Example: An Accumulated Mapping
You may immediately question the usability of the SP_map procedure for larger lists. For example, suppose you provide an input list containing 100 elements. SP_map returns one result set for each executed statement?and hence for each input element. That’s a lot of little windows to scroll through! For larger lists, SP_map provides an @Accumulate flag that collapses the results into a single result set. Though conceptually simple, this minor difference is a major job for SP_map. It rewrites your query to add a new first column that contains your input element. Otherwise, you would have no way to correlate the inputs to the outputs (see Figure 4).
 |
? |
| Figure 4. Accumulating a Mapping: Enabling the @Accumulate flag collapses multiple result sets into a more convenient single result set. |
Turning on the @Accumulate flag imposes a constraint on your SELECT statement?that all returned fields must be named. In other words, the simple ‘select count(*) from ‘ statement no longer works. You must instead use something like ‘select count(*) as Count from ‘, as shown in Figure 4. If you forget, SQL Server will gently but insistently scold you with an error similar to this:
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.You can view the code by setting the @Verbose flag to 1 and re-executing the command. Here is the internal code generated from the original query:
SELECT Convert(varchar(128),'Person.Address') as ItemName, count(*) as Rows INTO #Map_Temp_Table from Person.Address INSERT INTO #Map_Temp_Table SELECT 'Person.Contact', count(*) as Rows from Person.Contact INSERT INTO #Map_Temp_Table SELECT 'Production.Culture', count(*) as Rows from Production.Culture INSERT INTO #Map_Temp_Table SELECT 'Sales.Customer', count(*) as Rows from Sales.Customer select * from #Map_Temp_Table drop table #Map_Temp_TableAs you can see, SP_map uses an intermediate temporary table to collect the individual result sets. You can see this more clearly if you increase the verbosity from 1 to 2, which reveals the intermediate CREATE and UPDATE templates in addition to the actual block of executed code. SP_map generates these intermediate templates directly from your statement template, and then uses these intermediate templates to map substitutions from the input list:
-- TEMPLATE (create): SELECT Convert(varchar(128), '') as ItemName, count(*) as Rows INTO #Map_Temp_Table from -- TEMPLATE (update): INSERT INTO #Map_Temp_Table SELECT '', count(*) as Rows from For the first element in your input list, SP_map uses the CREATE template to dynamically define a temporary table and store the first result set. For subsequent entries it uses the UPDATE template to append rows to the temporary table. When finished, it selects everything from the temporary table using a single SELECT statement?yielding a single result set?and deletes the temporary table.
Example: An Accumulated Mapping Revisited
You’ve seen how to accumulate a simple SELECT statement across a series of arguments. But it can also be useful in conjunction with stored procedures such as sp_who, which reports on current users, or sp_spaceused, which returns row count and disk space information about tables, views, or queues. For example, this code uses SP_map to invoke sp_spaceused on two tables:
EXEC SP_map @StmtTemplate = "execute sp_spaceused ", @MainArray = 'Users, Permissions', @Accumulate = 0The @Accumulate flag, as you have seen, aggregates multiple result sets into one. But the code needed to do this with stored procedures is quite different than that for simple SELECT statements. If you modify the above line of code to enable @Accumulate and set @Verbose to 1, SP_map returns this generated code:
-- TEMPLATE (create): SELECT * INTO #Map_Temp_Table FROM OPENROWSET ('SQLOLEDB', 'Server=(local)SqlExpress;TRUSTED_CONNECTION=YES;', 'set fmtonly off execute dbo.sp_spaceused ') -- TEMPLATE (update): INSERT INTO #Map_Temp_Table SELECT * FROM OPENROWSET ('SQLOLEDB', 'Server=(local)SqlExpress;TRUSTED_CONNECTION=YES;', 'set fmtonly off execute dbo.sp_spaceused ') SELECT * INTO #Map_Temp_Table FROM OPENROWSET ('SQLOLEDB','Server=(local)SqlExpress;TRUSTED_CONNECTION=YES;', 'set fmtonly off execute dbo.sp_spaceused Users') INSERT INTO #Map_Temp_Table SELECT * FROM OPENROWSET ('SQLOLEDB','Server=(local)SqlExpress;TRUSTED_CONNECTION=YES;', 'set fmtonly off execute dbo.sp_spaceused Permissions') select * from #Map_Temp_Table drop table #Map_Temp_TableWhen invoked conventionally (without an SP_map wrapper), typical stored procedures return single result sets. But one special stored procedure?sp_msForEachTable?returns multiple result sets (in fact, one result set per table). To show the space used for all tables, for example, this standard invocation returns a lot of result sets:
EXEC sp_msForEachTable "EXEC sp_spaceused '?'"SP_map can also convert that output into a single result set with only one syntactic change: since you are now embedding the preceding statement inside a string you must double the single quotes to retain the original statement:
EXEC SP_map @StmtTemplate = 'EXEC sp_msForEachTable "EXEC sp_spaceused ''?''"', @Accumulate = 1Note that the only time that you must not supply @MainArray as a parameter is when you’re wrapping multiple-result-set stored procedures in SP_map. @MainArray, in this situation, is already defined implicitly as the set of all table names. Here’s the generated code for the preceding statement:
-- TEMPLATE (create): SELECT * INTO #Map_Temp_Table FROM OPENROWSET ( 'SQLOLEDB', 'Server=(local)SqlExpress;TRUSTED_CONNECTION=YES;', 'set fmtonly off EXEC dbo.sp_msForEachTable "select convert(varchar(128), ''?'') as Name"') TRUNCATE TABLE #Map_Temp_Table INSERT INTO #Map_Temp_Table EXEC dbo.sp_msForEachTable "select convert(varchar(128), '?') as Name" SELECT * INTO #Map_Temp_Table FROM OPENROWSET ( 'SQLOLEDB', 'Server=(local)SqlExpress;TRUSTED_CONNECTION=YES;', 'set fmtonly off EXEC dbo.sp_msForEachTable "select convert(varchar(128), ''?'') as Name" ') TRUNCATE TABLE #Map_Temp_Table INSERT INTO #Map_Temp_Table EXEC dbo.sp_msForEachTable "select convert(varchar(128), '?') as Name" select * from #Map_Temp_Table drop table #Map_Temp_TableThis time, the code generates a CREATE template only?no UPDATE template. Also, the CREATE template has no placeholders. SP_map is not iterating through a list of stored procedure calls as with normal stored procedures; the power-packed sp_msForEachTable stored procedure does the iteration all by itself.
Example: A Permuted Mapping
For the next example, create a simple table called Permissions. The DDL for the table is:
CREATE TABLE [dbo].[Permissions]( [IDPermission] [int] IDENTITY(1,1) NOT NULL, [IDUser] [int] NOT NULL, [IDPermissionType] [int] NOT NULL, [PermGranted] [bit] NOT NULL ) Consider this scenario: The Permissions table you just created contains permissions for each user in your system. Each row in the table specifies a user ID, a permission type, and whether the permission is granted or not. You have a list of six new user IDs, and you want to grant each the same set of eight permissions. To do that, you’d have to insert 48 rows in the Permissions table, one row for each user for each permission. But you can accomplish this with a single call to SP_map. The solution below shows five lines of code only because it uses variables for clarity; however, if you put the lists directly in the procedure call, it really requires only one line of code. This example is two-dimensional, requiring two placeholders. Contrast that with the previous example, which used but a single list and a single placeholder in the template.
DECLARE @USERS VARCHAR(50) SET @USERS = '913,1425,11223,739,1234,1001' DECLARE @PERMS VARCHAR(50) SET @PERMS = '2,3,5,7,11,13,17,19' EXEC SP_map @StmtTemplate='INSERT INTO Permissions VALUES(, , 1)', @MainArray=@USERS, @AuxArray=@PERMS, @Permute=1, @Verbose=1, @Process=0 With @Verbose set to 1, SP_map emits this generated code:
-- INSERT INTO Permissions VALUES(913, , 1) -- INSERT INTO Permissions VALUES(1425, , 1) -- INSERT INTO Permissions VALUES(11223, , 1) -- INSERT INTO Permissions VALUES(739, , 1) -- INSERT INTO Permissions VALUES(1234, , 1) -- INSERT INTO Permissions VALUES(1001, , 1) Note that each line begins with the SQL comment marker. Why? Because these are not executable SQL statements! SP_map has replaced the placeholder in the @StmtTemplate with values from the @USERS list, but not the placeholder. SP_map operates on two-dimensional combinations in the obvious way: For each user, it does something (which creates the statements above), and then it does something for each permission. In other words, this is just two nested loops. Therefore, setting @Verbose to 1 shows details of the outer loop, while setting @Verbose to 2 also shows details of the inner loop. The end product of the inner loop is a set of finished DML statements, so these are not prefaced with the comment marker:
-- INSERT INTO Permissions VALUES(913, , 1) INSERT INTO Permissions VALUES(913, 2, 1) INSERT INTO Permissions VALUES(913, 3, 1) INSERT INTO Permissions VALUES(913, 5, 1) INSERT INTO Permissions VALUES(913, 7, 1) INSERT INTO Permissions VALUES(913, 11, 1) INSERT INTO Permissions VALUES(913, 13, 1) INSERT INTO Permissions VALUES(913, 17, 1) INSERT INTO Permissions VALUES(913, 19, 1) -- INSERT INTO Permissions VALUES(1425, , 1) INSERT INTO Permissions VALUES(1425, 2, 1) INSERT INTO Permissions VALUES(1425, 3, 1) INSERT INTO Permissions VALUES(1425, 5, 1) INSERT INTO Permissions VALUES(1425, 7, 1) INSERT INTO Permissions VALUES(1425, 11, 1) INSERT INTO Permissions VALUES(1425, 13, 1) INSERT INTO Permissions VALUES(1425, 17, 1) INSERT INTO Permissions VALUES(1425, 19, 1) -- INSERT INTO Permissions VALUES(11223, , 1) INSERT INTO Permissions VALUES(11223, 2, 1) INSERT INTO Permissions VALUES(11223, 3, 1) INSERT INTO Permissions VALUES(11223, 5, 1) INSERT INTO Permissions VALUES(11223, 7, 1) INSERT INTO Permissions VALUES(11223, 11, 1) . . .Figure 5 shows the contents of the Permissions table after the preceding code has executed.
This example so far has dealt with a two-dimensional scenario with the @Permute flag turned on. What happens if you turn it off?
| Author’s Note: Empty the Permissions table to start fresh before executing the code shown in Figure 6. |
When @Permute is off, the two lists are constrained to be of the same cardinality, so you would need to modify your user and permissions list to have the same number of elements, as in Figure 6.
You can see in Figure 6 that the procedure takes the elements of @MainArray and @AuxArray in a pair-wise fashion, until both are used up. A key observation is that when you run the procedure with @Permute set to 0, order matters! With the permuted example shown previously, the ordering of the elements in the two lists was not significant. (Note that order may matter whether you activate @Permute or not, because database operations almost always produce side effects that can cause changes in a persistent database.)
Example: Mapping With Dynamic Inputs
For this next example, consider if, instead of passing a hard-coded list of user IDs, you select a subset of users from a Users table. To follow along, create a basic Users table and populate it with this code:
CREATE TABLE [dbo].[Users]( [IDUser] int IDENTITY(1,1) NOT NULL, [LastName] varchar(50) NULL, [FirstName] varchar(50) NULL ) INSERT INTO dbo.Users VALUES ('Smith', 'A') INSERT INTO dbo.Users VALUES ('Jones', 'C') INSERT INTO dbo.Users VALUES ('Smith', 'B') INSERT INTO dbo.Users VALUES ('Smith', 'D') INSERT INTO dbo.Users VALUES ('Smithson', 'W') INSERT INTO dbo.Users VALUES ('Miller', 'R') INSERT INTO dbo.Users VALUES ('Smith', 'Q') INSERT INTO dbo.Users VALUES ('Rice', 'O') This code again uses the Permissions table created earlier, but draws users from the Users table rather than a constant list:
DECLARE @PERMS VARCHAR(50) SET @PERMS = '2,3,5' EXEC SP_map 'INSERT INTO Permissions VALUES( , , 1)', @MainArray='SELECT IDUser from dbo.UsersWHERE LastName LIKE ”Smith%”’, @AuxArray=@PERMS, @Permute=1, @Verbose=1
Note that the user list is still passed in through the @MainArray parameter, but instead of a constant list, it is a SELECT statement. Because the @Permute flag is on, SP_map instantiates all combinations of users like ‘Smith%’ (there are five) with the three-element @AuxArray list. Figure 7 shows the results.
When using dynamic inputs, keep these points in mind:
- You may use a dynamic list in place of a constant list for either @MainArray or @AuxArray.
- You may use either a SELECT statement or an EXECUTE statement to formulate your query.
- The query must return a result set containing only a single column of type VARCHAR(128).
Notes On Accumulation
When you accumulate results, there are a few guidelines you should follow.
Ensure your first result set allows room for subsequent result sets.
The @Accumulate flag for SP_map is quite useful?particularly for queries that return many small result sets. You do need to be cognizant, however, that SP_map may not be able to do what you want without some additional guidance. Take, for example, this conventional query to generate a list of all table names:
EXEC sp_msForEachTable " select '?' "(Yes, it would be much more direct to just use SELECT name FROM sysobjects WHERE xtype = ‘U’ to generate a list of table names in a single result set, but this serves as a simple example for discussion.)
That query generates many result sets. You will probably find yourself wanting to feed any query that makes use of sp_msForEachTable to SP_map just to have your results reduced to a convenient, single result set. The first step needed, as already discussed, is to name the anonymous column:
EXEC sp_msForEachTable " select '?' as TableName"Next, you need to put that query into a string, so you must double the quotes to retain them:
@StmtTemplate = ' EXEC sp_msForEachTable " select ''?'' as TableName" 'Now, here’s the subtle point that might leave you perplexed without this exposition: If you just pass that to SP_map . . .
EXEC SP_map @StmtTemplate = ' EXEC sp_msForEachTable " select ''?'' as TableName" ', @Accumulate = 1. . . you get an error similar to this?unless you are very lucky:
Msg 8152, Level 16, State 14, Line 1 String or binary data would be truncated.Why? Recall that the accumulation is done by creating a temporary table on the fly, whose definition is inferred from the first result set. Assume the result set contains a string. Unless that result set just happens to have the longest string in that field of any value from all the following results sets, the subsequent result sets attempt to put a string that is too long into the field, yielding the above error. You must provide explicit guidance to tell SP_map that the name field should have room for a longer string, irrespective of how short or how long the value in the first result set is. Here is one possible solution; if your tables have longer names, use a larger conversion value:
EXEC SP_map @StmtTemplate = 'EXEC sp_msForEachTable "select convert( varchar(128), ''?'') as Name" ', @Accumulate = 1Ensure all your result sets have the same shape.
Using an @StmtTemplate such as SELECT * FROM will work only if each table in your argument list has the same number of columns and the datatypes match. In other words, you cannot accumulate a result set containing five columns with a second result set containing three columns. Again, accumulation works by dynamically generating a temporary table from the inferred structure of the first result set. Each subsequent result set is merged into the temporary table?so each column must be compatible with the temporary table structure.
Use @AccumulateFilter to filter the accumulated result set.
Here’s one final tip to help you leverage the power of SP_map together with sp_msForEachTable. You can use sp_msForEachTable to set your universe of discourse to all tables, then use SP_map to filter this set down to just those elements you are interested in. Doing this without SP_map gives you a collection of empty result sets interspersed with some populated ones, as with this query to list all the tables containing “Vendor” as part of the table name. Using the AdventureWorks database, this returns 72 result sets where 68 of them are empty and the remaining 4 each contain a single row:
EXEC sp_msForEachTable "select convert(varchar(128), '?') as Name where '?' like '%Vendor%' "Simply wrapping that with SP_map and enabling @Accumulate automatically filters out any empty result sets. Using the AdventureWorks database, this returns a single result set with 4 rows:
EXEC SP_map @StmtTemplate = 'EXEC sp_msForEachTable "select convert(varchar(128), ''?'') as Name where ''?'' like ''%Vendor%'' " ', @Accumulate = 1That is great when feeding a SELECT statement to sp_msForEachTable, but what if you want to feed it a stored procedure? Recall that when you enable @Accumulate, SP_map collects its result sets into a temporary table and then emits the contents of that populated table with a simple SELECT statement at the end:
SELECT * FROM #Map_Temp_TableYou may add a filtering predicate to this statement via the @AccumulateFilter parameter. Returning to the example with sp_spaceused, this invocation displays only the non-empty tables:
EXEC SP_map @StmtTemplate = "execute sp_spaceused ", @MainArray = 'Users, Permissions', @Accumulate = 1, @AccumulateFilter = "rows > 0"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, @Processwhere
@StmtTemplate ::= @MainArray ::= "'" {Table 1 provides the details for each parameter.
| 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 and 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 placeholder should not be present in the @StmtTemplate. |
| @Permute | bit | 0 | If turned on, then all combinations of and 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.
|
| @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.”
