Stored procedure syntax Error(MSSQL)

Question

Below mentioned stored procedure is giving error while creating

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

Can anyone please tell me the 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

Chenopod

Answer 1

That looks like a procedure originally used for SQL Server 2000 where pivot was not a keyword. Change the below section to use [pivot] instead.

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

You should probably also use sysname data type for the @table parameter, use the quotename function when concatenating the table and column names and use nvarchar rather than varchar.

These are all suggestions aimed at reducing SQL injection possibilities as well as allowing you to deal with non standard object names. Currently sysname is nvarchar(128). By using sysname instead of nvarchar(128) though you won't have to update the procedure if this changes in a future version.

Using varchar(100) means that your procedure won't be able to handle (valid) object names greater than 100 characters. As well as not being able to handle valid names containing non standard characters.

The following is allowed in SQL Server

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

Even if you only name your tables and columns using ASCII characters keeping your parameters and variables as unicode will prevent issues such as the ʼ character (U+02BC) silently being converted to a regular apostrophe.

quotename will ensure that if you have any columns called Robert'); DROP TABLE Students; that these are escaped correctly as [Robert'); DROP TABLE Students;] as well as dealing with any embedded square brackets in object names.

Perhaps

thnks a lot. wt do you mean by sysname and quotename?? – Chenopod

Answer 2

Pivot is a SQL keyword. So you need to enclose it within square brackets.

Quieten