SQL Server Techniques for Creating a Web Reporting Page — Part 2


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.


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


Leave a comment

Your email address will not be published.