Error Stored Procedure Syntax (MSSQL)

Question

The following stored procedure gives an error while creating

Msg 156, Level 15, State 1, Procedure crosstab, Line 23
Incorrect syntax near the keyword 'pivot'.

Can someone tell me a mistake?

Below is the script:

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

Answer 1

This looks like the procedure originally used for SQL Server 2000, where pivotthere was no keyword. Change the section below to use [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

Perhaps you should also use the data type sysnamefor the parameter @table, use the function quotenamewhen concatenating table and column names and use nvarchar, not varchar.

, SQL-, . sysname - nvarchar(128). sysname nvarchar(128), , .

varchar(100) , () 100 . , .

SQL Server

CREATE TABLE "╚╦╩╗" ( "└┬┴┐" nvarchar(10)) 

ASCII, unicode, , ʼ (U + 02BC), .

quotename , Robert'); DROP TABLE Students;, [Robert'); DROP TABLE Students;], .

Answer 2

Pivot - SQL. .