INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

T-SQL Hints and Tips

Crosstab Query - PART II (Dynamic Execution) by donutman
Posted: 5 Jul 04 (Edited 12 Mar 05)

   In this version of the Crosstab query generator, the stored procedure, spCrossTabRun, will execute the dynamically generated query. Because this SP was designed to be exposed to "power" users who would then be able to dynamically execute SQL, code was added to check for WMD in the form of SQL injection attacks.  All of the extra formatting of the crosstab query has been removed from this version of the SP as it is never seen.
   The spCrossTabRun SP does the following:
  • guards against SQL injection attacks,
  • does extensive error checking of the input parameters,
  • uses RaisError to pass error messages back to the calling program,
  • allows functions to be placed outside and inside the aggregate function,
  • allows the pivot column to be a computed column,
  • allows the pivot column to include data with an embedded single quote and
  • creates a separate column named ôNULLSö when the pivot column includes a NULL.
   There are only three string parameters that must be entered in order for the SP to generate the code.  The parameters are explained in the order in which they should be entered.

@QueryTemplate
   This is a working (but stripped down) SELECT query on the object table(s).  It needs to include a GROUP BY clause for the summary column to be shown in each row.  Don't include the pivot column nor the aggregate column to be used for the pivot values.  Usually this means the only item in the Select list is the column(s) in the Group By clause.  The FROM clause can contain joins and derived tables.  IÆm sure there are limits beyond which the SP will not function, but you should find it quite flexible.

@AggregrateFunction
   This as you can guess is the aggregate function including the column on which it is to be performed, e.g. Sum(SalesAmount).  The column can also be a computed column, but do NOT name it nor ADD an alias within this parameter.

@PivotColumn
   This is the column that contains the data that is to become the columns of the pivot table or crosstab query, e.g. Year(SalesDate).  Note that you can indeed apply a function to the actual column.  The column can also be a computed column, but again do NOT name it nor ADD an alias within this parameter.
***************************************
CODE FOR STORED PROCEDURE FOLLOWS:
***************************************
CREATE PROCEDURE spCrossTabRun
   @QueryTemplate varchar(8000),
   @AggregateFunction varchar(100),
   @PivotColumn varchar(100)
AS
   SET NoCount ON
   SET Ansi_Warnings Off
   DECLARE @SqlCase varchar(8000),
           @SqlPivot varchar(8000), @Delimiter varchar(1)
   DECLARE @ChrPos int --Character position
   DECLARE @Aggs varchar(64), @BeforeAgg varchar(8000),
           @AfterAgg varchar (8000)
   DECLARE @LftPos int, @RgtPos int

   -- Check for valid parameters in this section --
   EXEC('SET PARSEOnly ON; ' + @QueryTemplate)
   IF @@Error>0 BEGIN
      RAISERROR ('There is an error in Parameter #1',15,-1)
      RETURN
   END

   SET @ChrPos=CharIndex('select ',@QueryTemplate)
   IF @ChrPos=0 BEGIN
      RAISERROR ('You did not enter a SELECT clause in the 1st parameter',15,-1)
      RETURN
   END

   SET @BeforeAgg='SET PARSEOnly ON; ' + Stuff(@QueryTemplate,@ChrPos+7,0,@AggregateFunction+', ')
   EXEC (@BeforeAgg)
   IF @@Error>0 BEGIN
      RAISERROR ('There is an error in Parameter #2.',15,-1)
      RETURN
   END

   IF CharIndex(' from ',@QueryTemplate)=0 BEGIN
      RAISERROR ('You did not enter a FROM clause in the 1st parameter',15,-1)
      RETURN
   END

   IF CharIndex(' group by ',@QueryTemplate)=0 BEGIN
      RAISERROR ('You did not enter a GROUP BY clause in the 1st parameter',15,-1)
      RETURN
   END

--This section is devoted to the @AggregateFunction parameter only --
   CREATE TABLE #AggFunctions (Agg varchar(11))
   SET @Aggs='AVG(,COUNT(,COUNT_BIG(,MAX(,MIN(,STDEV(,STDEVP(,SUM(,VAR(,VARP(,'
   --Build a temporary table of all the agg functions
   WHILE @Aggs<>' BEGIN
      SET @ChrPos=CharIndex(',',@Aggs)
      INSERT INTO #AggFunctions
         SELECT Left(@Aggs,@ChrPos-1)
      SET @Aggs=SubString(@Aggs,@ChrPos+1,64)
   END

   SET @ChrPos=0      
   SET @AggregateFunction=LTrim(Rtrim(@AggregateFunction))
   --Get rid of unnecessary blanks
   WHILE Len(@AggregateFunction)<>@ChrPos BEGIN
      SET @ChrPos=Len(@AggregateFunction)
      SET @AggregateFunction=Replace(@AggregateFunction,' (','(')
      SET @AggregateFunction=Replace(@AggregateFunction,'( ','(')
      SET @AggregateFunction=Replace(@AggregateFunction,') ',')')
      SET @AggregateFunction=Replace(@AggregateFunction,' )',')')
   END
   SET @LftPos=0  --Find the location of the aggregate function
   SELECT @RgtPos=CharIndex(Agg,@AggregateFunction),@LftPos=@LftPos+@RgtPos
      FROM #AggFunctions WHERE CharIndex(Agg,@AggregateFunction)>0
   DROP TABLE #AggFunctions
   IF @RgtPos<>@LftPos
      RAISERROR ('You cannot have more than one aggregate function in Parameter #2.',15,-1)
   IF @RgtPos=0
      RAISERROR ('You did not enter an aggregate function in Parameter #2.',15,-1)
   --Get any non-agg functions that occur prior to the agg function
   SET @BeforeAgg=Left(@AggregateFunction,@RgtPos-1)
   SET @AggregateFunction=SubString(@AggregateFunction,@RgtPos,8000)
   WHILE Left(@BeforeAgg,1)='(' AND Right(@AggregateFunction,1)=')' BEGIN
      SET @BeforeAgg=SubString(@BeforeAgg,2,8000)
      SET @AggregateFunction=Left(@AggregateFunction,Len(@AggregateFunction)-1)
   END
   SET @ChrPos=1
   SET @LftPos=0
   --Count the number of parentheses before the agg function
   WHILE @ChrPos<=Len(@BeforeAgg) BEGIN
      IF SubString(@BeforeAgg,@ChrPos,1)='('
         SET @LftPos=@LftPos+1
      IF SubString(@BeforeAgg,@ChrPos,1)=')'
         SET @LftPos=@LftPos-1
      SET @ChrPos=@ChrPos+1
   END
   SET @ChrPos=Len(@AggregateFunction)
   --Count back from the right the same number of parentheses from above
   WHILE @LftPos>-1 BEGIN  
      IF SubString(@AggregateFunction,@ChrPos,1)='(' SET @LftPos=@LftPos+1
      IF SubString(@AggregateFunction,@ChrPos,1)=')' SET @LftPos=@LftPos-1
      SET @ChrPos=@ChrPos-1
      IF @ChrPos=0 BREAK
   END
   IF @ChrPos=0 BEGIN
      RAISERROR ('Your parenthesis are not balanced in Parameter #2.',15,-1)
      RETURN
   END
   --Get the tail end of non-agg functions
   SET @AfterAgg=SubString(@AggregateFunction,@ChrPos+2,8000)
   --This is the agg function with any INTERIOR functions  
   SET @AggregateFunction=Left(@AggregateFunction,@ChrPos+1)  
-- Finished with @AggregateFunction --

   --Hunt for SQL injections that use WMD.
   CREATE TABLE #WMD (Weapon varchar(128))
   INSERT INTO #WMD
      SELECT ' alter ' UNION
      SELECT ' bcp ' UNION
      SELECT ' delete ' UNION
      SELECT ' drop ' UNION
      SELECT ' exec ' UNION
      SELECT ' exec(' UNION
      SELECT ' execute ' UNION
      SELECT ' execute(' UNION
      SELECT ' insert ' UNION
      SELECT ' openquery ' UNION
      SELECT ' restore ' UNION
      SELECT ' shutdown ' UNION
      SELECT ' truncate ' UNION
      SELECT ' update ' UNION
      --The sp_ and xp_ are listed here and again in the sysobjects
      --insert from below, so that if you like,
      --you can remove the sysobjects insert,
      --but still keep the most dangerous ones in #WMD.
      SELECT ' sp_sqlexec ' UNION  
      SELECT ' sp_executesql ' UNION  
      SELECT ' xp_cmdshell '  
   INSERT INTO #WMD
      SELECT ';'+LTrim(Weapon) FROM #WMD UNION
      SELECT [Name] FROM Master.dbo.sysobjects
         WHERE xType='P' OR xType='X'

   -- Strip out literals from the parameters
   -- so they are not identified as keywords.
   DECLARE @AllParameters varchar(8000)
   DECLARE @ParseParam varchar (8000)
   DECLARE @Cnt int, @End int
   SET @AllParameters=' '+@QueryTemplate+' '+@AggregateFunction+' '+@PivotColumn+' '+Char(39)
   SET @ParseParam='
   SET @ChrPos=1
   SET @Cnt=1
   WHILE @ChrPos<Len(@AllParameters) BEGIN
      SET @Cnt=@Cnt+1
      SET @End=CharIndex(Char(39),@AllParameters,@ChrPos)
      IF @Cnt % 2 = 0 SET @ParseParam=@ParseParam+SubString(@AllParameters,@ChrPos,@End-@ChrPos)
      SET @ChrPos=@End+1
   END
   --Now check for WMD
   SET @SqlPivot='                         
   SELECT @SqlPivot=@SqlPivot+
         CASE CharIndex(Weapon,@ParseParam)
            WHEN 0 THEN ' ELSE ', '+LTrim(Rtrim(Weapon)) END
      FROM #WMD

   DROP TABLE #WMD
   IF Len(@SqlPivot)>0 BEGIN
      SET @SqlPivot='Your parameters contain a possible SQL injection attack:'
                 +Char(13)+SubString(@SqlPivot,3,8000)+'.'
      RAISERROR (@SqlPivot,15,-1)
      RETURN
   END

   -- Use @SqlPivot to modify @QueryTemplate by making
   -- the first column the pivot column, run the query and insert
   -- the PivotColumn values into a global temporary table.  
   -- The values will become the names of the cross tab columns.
   SET @SqlPivot=@QueryTemplate
   SET @ChrPos = CharIndex('select ',@SqlPivot)
   SET @SqlPivot = Stuff(@SqlPivot,@ChrPos+7,0,@PivotColumn + ' as PivotColumn, ')
   SET @ChrPos = CharIndex(' from ',@SqlPivot)
   SET @SqlPivot = Stuff(@SqlPivot,@ChrPos,0,' INTO ##CrossTab ')
   SET @ChrPos = CharIndex(' group by ',@SqlPivot)
   WHILE CharIndex(' group by ',@SqlPivot,@ChrPos+1)>0 --Find the last GROUP BY clause.
      SET @ChrPos = CharIndex(' group by ',@SqlPivot,@ChrPos+1)
   SET @SqlPivot = Stuff(@SqlPivot,@ChrPos+10,0,@PivotColumn+', ')

   -- Check to see if someone else is using ##CrossTab
   -- at the same time and wait 10 seconds.
   IF Exists (SELECT * FROM TempDB.dbo.SysObjects
         WHERE [ID] = Object_ID(N'TempDB.[dbo].[##CrossTab]') AND xType='U')
      WAITFOR DELAY '000:00:10'
   IF Exists (SELECT * FROM TempDB.dbo.SysObjects
         WHERE [ID] = Object_ID(N'TempDB.[dbo].[##CrossTab]') AND xType='U') BEGIN
      RAISERROR ('There is another global temporary table named ##CrossTab.  Cannot continue.',15,-1)
      RETURN
   END

   EXEC (@SqlPivot)
   IF NOT Exists (SELECT * FROM TempDB.dbo.SysObjects
         WHERE [ID] = Object_ID(N'TempDB.[dbo].[##CrossTab]') AND xType='U') BEGIN
      RAISERROR ('There is an error in Parameter #3.',15,-1)
      RETURN
   END

   -- Create a template for each CASE clause that
   -- will needed for the crosstab columns in the SELECT clause.
   -- The @AfterAgg gets added here, but the @BeforeAgg is inserted later.
   SET @AggregateFunction=Stuff(@AggregateFunction, Len(@AggregateFunction), 1, ' END)' + @AfterAgg)

   -- Determine whether or not to use a ' to
   -- surround the comparison values in the CASE clause.
   -- Varchar, Char and Date data types require the
   -- apostrophe whereas numbers do not require a delimiter.
   SELECT @Delimiter = Left('', COUNT(*))
       FROM ##CrossTab  WHERE IsNumeric(Cast(PivotColumn AS varchar(8000)))=0

   -- Generate all of the CASE statements needed in the SELECT clause.
   SELECT @SqlCase=Coalesce(@SqlCase,') +', [' + IsNull(Convert(varchar(8000), PivotColumn),'NULLS') + '] = ' +
         @BeforeAgg+Stuff(@AggregateFunction,CharIndex( '(', @AggregateFunction )+1, 0, 'CASE ' + @PivotColumn + ' WHEN '
         + @Delimiter + IsNull(Replace(Convert(varchar(8000), PivotColumn),'','''),'NULL') + @Delimiter + ' THEN ' )
       FROM (SELECT DISTINCT TOP 100 PERCENT PivotColumn FROM ##CrossTab ORDER BY PivotColumn) DistinctPivots

   ---Fix syntax of NULLS to say IS NULL instead of WHEN NULL.
   SET @ChrPos = CharIndex('[NULLS]',@SqlCase)
   IF @ChrPos>0 BEGIN
      SET @ChrPos = CharIndex('(CASE ',@SqlCase,@ChrPos)
      SET @SqlCase = Stuff (@SqlCase,@ChrPos+6,0,'WHEN ')
      SET @ChrPos = CharIndex(' WHEN ',@SqlCase,@ChrPos+10)
      SET @SqlCase = Stuff (@SqlCase, @ChrPos+1,9+2*Len(@Delimiter),'IS NULL')
   END

   DROP TABLE ##CrossTab

   -- Insert the CASE statements into the SELECT clause
   -- that was provided by the user.
   SELECT @QueryTemplate=Stuff(@QueryTemplate, CharIndex(' FROM ', @QueryTemplate), 0, @SqlCase +  '  ')
   IF Len(@QueryTemplate)=8000
      RAISERROR ('The length of the query string is too long.',15,-1)
   ELSE
      EXEC (@QueryTemplate)
SET Ansi_Warnings ON
GO
***************************************

Example 1 uses the pubs database illustrates the point that a function can be placed around the aggregate function in parameter #2.

CODE

EXEC spCrossTabRun
'select title from pubs.dbo.titles t inner join pubs.dbo.sales s on (s.title_id=t.title_id) group by title',
'IsNull(sum(qty),0)',
'stor_id'
   Results of Example I:

CODE

TITLE        6380   7066   7067   7131   7896   8042
But Is It....   0      0      0      0      0     30
Computer P...   0      0      0     20      0      0
Cooking wi...   0      0      0      0      0     25
Emotional....   0      0      0     25      0      0
Fifty Year...   0      0     20      0      0      0
Is Anger t...   3     75     10     20      0      0
Life Witho...   0      0      0     25      0      0
Onions, Le...   0      0     40      0      0      0
Prolonged....   0      0      0     15      0      0
Secrets of...   0     50      0      0      0      0
Silicon Va...   0      0      0      0     10      0
Straight T...   0      0      0      0     15      0
Sushi, Any...   0      0     20      0      0      0
The Busy E...   5      0      0      0      0     10
The Gourme...   0      0      0     25      0     15
You Can Co...   0      0      0      0     35      0
   Example II illustrates that complex parameters (including a computed pivot column) are handled properly.

CODE

EXEC spCrossTabRun
   'select pub_name as Publisher, count(qty) as Orders, sum(qty) as Qty from pubs.dbo.sales s inner join pubs.dbo.titles t on (s.title_id=t.title_id) inner join pubs.dbo.publishers p on (p.pub_id=t.pub_id) where round(price*qty/100,0)*100<500 group by pub_name',
   'avg(case when price<5 then 5 else price end)',
   'round(price*qty/100,0)*100'
   Results of example II

CODE


Publisher             Order Qty  0.00 100.00 200.00   300.00 400.00

Algodata Infosystems    6   135  NULL  19.99  19.99    15.97   NULL
Binnet & Hardley        7   150     5   5     15.97    14.99   21.59
New Moon Books          8   208 10.95   7.975  8.6466  19.99   NULL
   Example III uses the Northwinds database illustrates that even pivot columns that contain embedded single quotes are also handled properly.

CODE

DECLARE @B varchar(200)
SET @B='B'
SET @B= 'SELect LastName FROM Northwind.dbo.Employees e INNER JOIN Northwind.dbo.Orders O ON (E.EmployeeID=O.EmployeeID) where ShipName<'+@B+' GROUP BY LastName'
EXEC spCrossTabRun
  @B,
  'count(lastname)',
  'Left(ShipName,8)'
   Results of example III

CODE


LastName Alfreds   Alfred's  Ana Truj  Antonio   Around

Callahan     0        0          0        0        1
Davolio      0        2          0        1        3
Dodsworth    0        0          0        0        2
King         0        0          1        2        0
Leverling    0        1          2        3        2
Peacock      0        2          1        1        4
Suyama       1        0          0        0        1

   Credit to robvolk at http://www.sqlteam.com/item.asp?ItemID=2955 and ESquared who insired me to push the envelope of his original work.
-Karl

Back to Microsoft SQL Server: Programming FAQ Index
Back to Microsoft SQL Server: Programming Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close