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

Pages: 1 2 3


No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |