Login | Register   
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Bring Array Mapping Capabilities to SQL : Page 3

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


advertisement
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 <MAIN_ARG>' statement no longer works. You must instead use something like 'select count(*) as Count from <MAIN_ARG>', 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_Table

As 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), '<MAIN_ARG>') as ItemName, count(*) as Rows INTO #Map_Temp_Table from <MAIN_ARG> -- TEMPLATE (update): INSERT INTO #Map_Temp_Table SELECT '<MAIN_ARG>', count(*) as Rows from <MAIN_ARG>

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 <MAIN_ARG>", @MainArray = 'Users, Permissions', @Accumulate = 0

The @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 <MAIN_ARG>') -- TEMPLATE (update): INSERT INTO #Map_Temp_Table SELECT * FROM OPENROWSET ('SQLOLEDB', 'Server=(local)\SqlExpress;TRUSTED_CONNECTION=YES;', 'set fmtonly off execute dbo.sp_spaceused <MAIN_ARG>') 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_Table

When 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 = 1

Note 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_Table

This 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.



Comment and Contribute

 

 

 

 

 


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

 

 

Sitemap