Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Posts
    202

    Unanswered: Error in cross tab statement

    Hi,

    I have a coding which state as below:

    CREATE PROCEDURE [dbo].[crossTab]
    @select varchar(8000),
    @sumfunc varchar(100),
    @pivot varchar(100),
    @table varchar(100)
    AS

    DECLARE @sql varchar(8000), @delim varchar(1)
    SET NOCOUNT ON
    SET ANSI_WARNINGS OFF

    EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE 1=2')
    EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE ' + @pivot + ' Is Not Null')

    SELECT @sql='' , @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )

    SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )
    WHEN 0 THEN '' ELSE '''' END
    FROM tempdb.information_schema.columns
    WHERE table_name='##pivot' AND column_name='pivot'

    SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' + stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN ' + @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM ##pivot
    DROP TABLE ##pivot

    SELECT @sql=left(@sql, len(@sql)-1)
    SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')

    EXEC (@select)
    SET ANSI_WARNINGS ON
    GO



    I just woud like why it come out error stated that

    Server: Msg 156, Level 15, State 1, Line 1
    Incorrect syntax near the keyword 'END'.

    when i key in

    exec crossTab 'select dbo,fgo,rgt from Table A', 'sum(fgo)', 'rgt', 'Table A'

    Please help me

    Thanx

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yet another attempt at a universal cross-tab function...

    Does your code error out if you comment out the "EXEC (@select)" line? If not, then try replacing it with "SELECT @select" or "PRINT @select" to see exactly what code is being executed that is causing the error.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •