CREATE PROCEDURE qryQtrCtryCustSalesDates 
        (@Start_Date nvarchar(128), @End_Date nvarchar(128)) 
AS 
SET NOCOUNT ON 
DECLARE @table_name sysname 
/* Assign tablename_paramvalue1[_paramvalue2]... */
SET @table_name = 'tbQtrCtryCustSalesDates' 
DECLARE @param0 nvarchar(128) 
SET @param0 = @Start_Date 
SET @param0 = REPLACE(@param0, ' ', '') 
SET @param0 = REPLACE(@param0, '/', '') 
SET @param0 = REPLACE(@param0, '-', '') 
SET @table_name = @table_name + '_' + @param0 
DECLARE @param1 nvarchar(128) 
SET @param1 = @End_Date 
SET @param1 = REPLACE(@param1, ' ', '') 
SET @param1 = REPLACE(@param1, '/', '') 
SET @param1 = REPLACE(@param1, '-', '') 
SET @table_name = @table_name + '_' + @param1 
/* Check the age of the table for regeneration */ 
IF EXISTS(SELECT table_name FROM information_schema.tables 
        WHERE table_name = @table_name) 
BEGIN 
        DECLARE @tabletime datetime 
        SET @tabletime = (SELECT crdate FROM sysobjects 
        WHERE name = @table_name) 
/* After testing the stored procedure, remove the two comment pairs below and replace 12 with the number of hours between successive rollups. */ 
        /* IF DATEDIFF(hh, @tabletime, GETDATE()) < 12 GOTO shortcut */
END 
/* Drop the existing table */
IF EXISTS(SELECT table_name FROM 
        information_schema.tables 
WHERE table_name = @table_name) 
        EXEC ('DROP TABLE ' + @table_name)
/* Create a temporary table from the function */ 
IF EXISTS(SELECT table_name FROM        
        information_schema.tables 
WHERE table_name = 'tbTempCT') 
        DROP TABLE tbTempCT 
CREATE TABLE tbTempCT (Country nvarchar(15), CompanyName 
        nvarchar(40), Quarter nvarchar(61), Sales money) 
/* Insert the view/function resultset */
INSERT INTO tbTempCT SELECT * FROM fnQtrCtryCustSalesDates(@Start_Date, @End_Date) 
/* Generate a new crosstab table */ 
EXEC ('CREATE TABLE ' + @table_name + '(Country nvarchar(15), 
        CompanyName nvarchar(40), [1999Q1] money DEFAULT 0, 
        [1999Q2] money DEFAULT 0, [1999Q3] money DEFAULT 0, 
        [1999Q4] money DEFAULT 0, [2000Q1] money DEFAULT 0, 
        [2000Q2] money DEFAULT 0, [2000Q3] money DEFAULT 0, 
        [2000Q4] money DEFAULT 0, Totals money DEFAULT 0)') 
/* Index is a bit out of order */
CREATE INDEX ixTempCT ON tbTempCT (Country, CompanyName) 
/* Add the full set of row headings */ 
EXEC ('INSERT INTO ' + @table_name + ' (Country, CompanyName) 
SELECT DISTINCT Country, CompanyName FROM tbTempCT 
ORDER BY Country, CompanyName') 
EXEC ('CREATE CLUSTERED INDEX ixTable ON ' + @table_name + 
' (Country, CompanyName)') 
/* Add the column header values */
EXEC ('UPDATE ' + @table_name + ' SET [1999Q1] = Sales 
FROM tbTempCT 
WHERE Quarter = ''1999Q1'' AND tbTempCT.Country = ' + @table_name + 
        '.Country AND tbTempCT.CompanyName = ' + 
        @table_name + '.CompanyName') 
EXEC ('UPDATE ' + @table_name + ' SET [1999Q2] = Sales 
FROM tbTempCT 
WHERE Quarter = ''1999Q2'' AND tbTempCT.Country = ' + @table_name + 
        '.Country AND tbTempCT.CompanyName = ' + 
        @table_name + '.CompanyName') 
/* EXEC statements here for five more quarters */
EXEC ('UPDATE ' + @table_name + ' SET [2000Q4] = Sales 
FROM tbTempCT 
WHERE Quarter = ''2000Q4'' AND tbTempCT.Country = ' + @table_name + 
        '.Country AND tbTempCT.CompanyName = ' + 
        @table_name + '.CompanyName') 
 /*Add row totals*/ 
EXEC ('UPDATE ' + @table_name + ' SET Totals = [1999Q1] + [1999Q2] + [1999Q3] + [1999Q4] + [2000Q1] + [2000Q2] + [2000Q3] + [2000Q4] ') 
/* Add a row of column totals */ 
EXEC ('INSERT ' + @table_name + '(Country) VALUES (''zzzzTotals'')') 
/* EXEC statements here for five more quarters
EXEC ('UPDATE ' + @table_name + ' SET [2000Q4] = (SELECT SUM([2000Q4]) FROM ' + @table_name +  ') WHERE Country = ''zzzzTotals''')
/* Add crossfoot (grand) total value */
EXEC ('UPDATE ' + @table_name + ' SET Totals = (SELECT SUM(Totals) 
FROM ' + @table_name + ') 
WHERE Country = ''zzzzTotals''') 
/* Drop the temporary table */ 
IF EXISTS(SELECT table_name FROM information_schema.tables 
        WHERE table_name = 'tbTempCT') 
        DROP TABLE tbTempCT 

shortcut: 
/* Return the table data with a fix-up for the totals row */
EXEC ('SELECT REPLACE(Country, ''zzzzTotals'', 
        ''Totals''), CompanyName, [1999Q1], [1999Q2], 
        [1999Q3], [1999Q4], [2000Q1], [2000Q2], [2000Q3], 
        [2000Q4] 
FROM ' + @table_name)