Pls help in making this proc work

Hi,

I just found a procedure to make pivot tables,But iam getting an error,can someone help in Overcoming the error.

The syntax is as below

CREATE PROCEDURE 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

just run the procedure and help me to fix the error.The error iam getting is

Msg 156, Level 15, State 1, Procedure crosstab, Line 23

Incorrect syntax near the keyword 'pivot'.

Any help is greatly apprecited.

Thanks,

SVGP

We will need a little more information to help you debug this.

Please provide DDL (Create statements for any tables involved), Sample data (In the form of Insert statements) and the command you are using to execute this stored Procedure (Including parameters)

|||

Hi,

There are so many tables involved with lots of data.

But the problem is not during the execution of proc,its while creating the proc,which doesnot involve any of the tables.

If still you require i will send them,

Thanks,

SVGP

|||

Where you have:

convert(varchar(100), pivot)

Change to

convert(varchar(100), [pivot])

PIVOT is a reserverd word in SQL2005.

(I assume that pivot is a column name?)

HTH!

|||

Yes you are right Thanks a lot

SVGP.

|||

Pls do the following corrections,

Code Snippet

CREATE PROCEDURE 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