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


Bring Array Mapping Capabilities to SQL : Page 6

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

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 = 1
Ensure all your result sets have the same shape.
Using an @StmtTemplate such as SELECT * FROM <MAIN_ARG> 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 = 1
That 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_Table
You 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 <MAIN_ARG>",
       @MainArray = 'Users, Permissions',
       @Accumulate = 1,
       @AccumulateFilter = "rows > 0"

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