Call now: (800) 766-1884  



 Home


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA



 Articles
 Services
 SQL Server Scripts
 Scripts Menu



 

 

 

 
  SQL Server Tips by Gama and Naughter

 

Testing the SP with the Pubs database

 

Example: Calculate the sales for each title per year and per month.

 

The code:
 

EXEC myTransformFixedColSP 'SUM'

, 'titles.price*sales.qty'

, 'SELECT YEAR(sales.ord_date) AS order_year'

, 'FROM  dbo.sales INNER JOIN dbo.titles ON dbo.sales.title_id = dbo.titles.title_id'

, 'ORDER BY YEAR(sales.ord_date)'

, 'GROUP BY YEAR(sales.ord_date)'

, 'MONTH(sales.ord_date)'

, '1,2,3,4,5,6,7,8,9,10,11,12'

, 'jan,feb,mar,apr,may,jun,jul,aug,sep,oct,nov,dec'

 

There are several months with no sales and it would be easier to read the data if those months were removed. It can be done by selecting which columns to show.

 

The code:

 

EXEC myTransformFixedColSP 'SUM'

, 'titles.price*sales.qty'

, 'SELECT YEAR(sales.ord_date) AS order_year'

, 'FROM  dbo.sales INNER JOIN dbo.titles ON dbo.sales.title_id = dbo.titles.title_id'

, 'ORDER BY YEAR(sales.ord_date)'

, 'GROUP BY YEAR(sales.ord_date)'

, 'MONTH(sales.ord_date)'

, '2,3,5,6,9,10,12'

, 'feb,mar,may,jun,sep,oct,dec'

 

This solution works if it is known in advance which columns to select, it would be easier if it would be done automatically. The solution is to use only the existing data that will determine the columns created by filtering the fixed column parameters.

 

This is the new UDF:

 

CREATE PROCEDURE myTransformVarColSP @AggFunction varchar(10), @AggData varchar(100), @Select  varchar(100), @From  varchar(100),

@GROUPBY varchar(100), @ORDERBY  varchar(100), @PIVOT  varchar(100), @IN1 varchar(100)=NULL, @IN2 varchar(100)=NULL

AS

--returns a crosstab query from a general table, with a variable number of columns

DECLARE @counter1 int, @counter2 int, @header1 varchar(100), @header2 varchar(100),

@dynamicSQL varchar(8000), @crlf char(2)--declare temporary variables and carriage return+line feed

DECLARE  @tmpTable TABLE(Header1 varchar(100), Header2 varchar(100))

IF @IN1 IS NOT NULL

      BEGIN

      IF @IN2 IS NULL --if there is no particular name for the columns, use the value

               SET @IN2=@IN1

      SET @IN1=@IN1+','--aliases are separated by ,

      SET @IN2=@IN2+','--aliases are separated by ,

      SET @counter1= CHARINDEX (',', @IN1)--update counter

      SET @counter2= CHARINDEX (',', @IN2)--update counter

      WHILE @counter1>0

               BEGIN

                         INSERT INTO @tmpTable

                         VALUES(LEFT(@IN1, @counter1-1), LEFT(@IN2, @counter2-1))

                         SET @IN1=RIGHT(@IN1, LEN(@IN1)-@counter1)--remove it from the input

                         SET @counter1= CHARINDEX (',', @IN1)--update counter

                         SET @IN2=RIGHT(@IN2, LEN(@IN2)-@counter2)--remove it from the input

                         SET @counter2= CHARINDEX (',', @IN2)--update counter

               END

      END

ELSE

      BEGIN

      CREATE TABLE #tmp (header varchar(100))

      INSERT INTO #tmp

      EXEC ('SELECT DISTINCT '+@PIVOT+' '+@From)

      INSERT @tmpTable

      SELECT header, header from #tmp

      DROP TABLE #tmp

      END

 

CREATE TABLE #tmp2 (header varchar(100))

INSERT INTO #tmp2

EXEC ('SELECT DISTINCT '+@PIVOT+' '+@From)

DELETE @tmpTable

WHERE header1 NOT IN (SELECT header from #tmp2)

DROP TABLE #tmp2

 
 

Are these SPís the perfect solution for using crosstab queries in SQL Server?

There is no perfect solution, but for Fixed Column Headings it is better to use a custom SP for each query because it would contain no dynamic SQL and the execution plan would be cached by the optimizer. One interesting application of the crosstab SPís is that the dynamic SQL can be displayed in Query Analyzer with a PRINT statement and used in the custom crosstab SPís. This will save a lot of work! Unfortunately, sometimes Fixed Column Headings crosstab queries are not enough and the calculations might be very intensive.
 


The above book excerpt is from:

Super SQL Server Systems
Turbocharge Database Performance with C++ External Procedures

ISBN: 0-9761573-2-2
Joseph Gama, P. J. Naughter

 http://www.rampant-books.com/book_2005_2_sql_server_external_procedures.htm
 

 

Burleson Consulting Remote DB Administration


 

 


 

 

 

 

 
Burleson is the America's Team

Note: The pages on this site were created as a support and training reference for use by our staff of DBA consultants.  If you find it confusing, please exit this page.

Errata?  SQL Server technology is changing and we strive to update our SQL Server support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:and include the URL for the page.
 


Burleson Consulting
SQL Server database support

 

Copyright © 1996 -  2013 by Vaaltech Web Services. All rights reserved.

Hit Counter