Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Stored procedure syntax Error(MSSQL)

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
like image 247
Thakur Avatar asked Sep 29 '10 13:09

Thakur


People also ask

What is the syntax of stored procedure in SQL Server?

The syntax to create a stored procedure in SQL Server (Transact-SQL) is: CREATE { PROCEDURE | PROC } [schema_name.] procedure_name [ @parameter [type_schema_name.] datatype [ VARYING ] [ = default ] [ OUT | OUTPUT | READONLY ] , @parameter [type_schema_name.]

Why am I getting a syntax error in SQL?

Misspellings are the most common cause for error in SQL. Unfortunately, SQL will not autocorrect mistyped keywords, tables, columns, or values. Check keyword spelling by referring to the documentation for the type of SQL you are using.

How do I view a stored procedure error in SQL Server?

Retrieving Error InformationERROR_STATE() returns the error state number. ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred. ERROR_LINE() returns the line number inside the routine that caused the error. ERROR_MESSAGE() returns the complete text of the error message.


2 Answers

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.

like image 122
Martin Smith Avatar answered Nov 04 '22 00:11

Martin Smith


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

like image 21
Joe Ratzer Avatar answered Nov 04 '22 01:11

Joe Ratzer