HOW IT WORKS
We alter the parameter @method’s default to “groupSalesWithTotal”. We also add the @type parameter. Within the existing “groupSales” code block, we add an insert statement and add another output column. “insert into #R (type,region,empid,sumSales) select type=@type…” Instead of returning the result set, SQL will now insert it into the temp table #R and add a new column TYPE. Where is the temp table #R created? The temp table is created in the main method. For example, suppose you are using Query Analyzer. You create a temp table. You perform an insert. You perform another insert. You perform a “select * from …”. SQL returns the 2 rows that you inserted. During this whole exercise, you are within the same session – meaning the temp table is available to the different statements. If we execute the main method and it executes the drone, we are within the same session – meaning the temp table created by the main method is available to the drone. The “groupSalesWithTotal” method code block: creates the temp table #R, calls upon “groupSales”, calls upon “groupSales” again for the total row, and returns the contents of #R.
EXAMPLE OUTPUT:
exec cubesp @group=’region’
TYPE REGION EMPID sumSales
Body East 0 4
Body North 0 24
Body South 0 46
Body West 0 64
Total 0 138
This concludes our article about SORT & TOTAL ROWS. I plan to write one more article demonstrating PIVOT/TRANSPOSE functionality.
About the Author
Louis Duc Nguyen lives in Dallas, TX and has a BS in Engineering and an MBA from University of Texas at Austin and Dallas. He is currently working for a major airline. He specializes in creating data-entry and reporting applications. Please address comments and hot job tips to Louis Nguyen at louisducnguyen@hotmail.com.
Copyright © 2002-2003 Louis Duc Nguyen All Rights Reserved
]]>