Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 The crosstab proc from SQLTeam Article not working

Author  Topic 

fuzzyip
Starting Member

35 Posts

Posted - 2008-08-09 : 02:40:05
I'm trying to mess around with the crosstab proc from

http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables

but I'm getting the error,

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


And its pointing to the part of the code that is...

SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' + 




the code that I'm pasting into sql server 2005 is...

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



Anyone have any ideas? i cant seem to find the error

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-09 : 02:48:18
Did you get the error while creating procedure or while executing?If latter, what were values passed for params?
Go to Top of Page

fuzzyip
Starting Member

35 Posts

Posted - 2008-08-09 : 02:52:40
quote:
Originally posted by visakh16

Did you get the error while creating procedure or while executing?If latter, what were values passed for params?



I got it trying to create the procedure
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-09 : 02:58:39
one reason can be because pivot is a keyword in sql 2005. try wrapping it within [].use [pivot] instead of pivot
Go to Top of Page

fuzzyip
Starting Member

35 Posts

Posted - 2008-08-09 : 14:02:12
quote:
Originally posted by visakh16

one reason can be because pivot is a keyword in sql 2005. try wrapping it within [].use [pivot] instead of pivot




I changed two instances of pivot to [pivot], you can see it here
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


And it ran successfully, the proc "crosstab" is now created.

However when I try to execute the crosstab with my table I get this error message,

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'pivot'.
Msg 208, Level 16, State 1, Line 1
Invalid object name '##pivot'.
Msg 208, Level 16, State 1, Procedure crosstab, Line 23
Invalid object name '##pivot'.



I'm looking at the code now, and I think there are more problems syntax wise in the proc code but I can't seem to figure it out, should I rename pivot to something else completely?

da_rate is nvarchar
da_date is smalldatetime
executiontime is nvarchar
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-09 : 14:25:41
change this line too and try

EXEC ('SELECT ' + @pivot + ' AS [pivot] INTO ##pivot FROM ' + @table + ' WHERE 1=2')
Go to Top of Page

fuzzyip
Starting Member

35 Posts

Posted - 2008-08-09 : 15:37:43
quote:
Originally posted by visakh16

change this line too and try

EXEC ('SELECT ' + @pivot + ' AS [pivot] INTO ##pivot FROM ' + @table + ' WHERE 1=2')





Great! It worked,


When I run the following code everything is working well,

execute crosstab 'select da_date from greatwolfcleanedup group by da_date order by da_date', 'min(da_rate)', 'executiontime', 'greatwolfcleanedup'


however when I add some where clauses like such

execute crosstab
'select da_date from greatwolfcleanedup where location like '%grand mound%' and roomtype like '%kbsx%' group by da_date order by da_date',

'min(da_rate)', 'executiontime', 'greatwolfcleanedup'


I get the following error message,

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '%'.

Pointing to the where statement, I've run the select statement by itself in a separate query and it seems to be fine, Is there something that I'm overlooking?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-09 : 15:38:53
execute crosstab
'select da_date from greatwolfcleanedup where location like ''%grand mound%'' and roomtype like '%kbsx%' group by da_date order by da_date',




E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

fuzzyip
Starting Member

35 Posts

Posted - 2008-08-09 : 15:44:38
quote:
Originally posted by Peso

execute crosstab
'select da_date from greatwolfcleanedup where location like ''%grand mound%'' and roomtype like '%kbsx%' group by da_date order by da_date',




E 12°55'05.25"
N 56°04'39.16"




I see, thank you so much, I would have never noticed. It has to be in those double single quotes because its enclosed inside another single quote?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-09 : 17:40:24
Learn your string delimiters!
execute crosstab	'select da_date from greatwolfcleanedup where location like ' + quotename('%' + @hotel + '%', '''') + ' and roomtype like ' + quotename('%' + @roomtype + '%', '''') + ' and da_date >= ''' + convert(varchar(25), @datebegin, 112) + ''' and da_date < ''' + convert(varchar(25), dateadd(day, 1, @dateend), 112) + ''' group by da_date order by da_date'
'min(da_rate)',
'executiontime',
'greatwolfcleanedup'



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

fuzzyip
Starting Member

35 Posts

Posted - 2008-08-09 : 17:42:10
I created another stored proc that executes this stored proc (so the user doesn't have to always type in the entire select statement each time)

using this code

create procedure gw
@hotel varchar(200),
@roomtype varchar(30),
@datebegin smalldatetime,
@dateend smalldatetime
as begin


execute crosstab
'select da_date from greatwolfcleanedup where location like ''%''+@hotel+''%'' and roomtype like ''%''+@roomtype+''%'' and da_date >= @datebegin and da_date < dateadd (d, +1, @dateend) group by da_date order by da_date', 'min(da_rate)', 'executiontime', 'greatwolfcleanedup'


end


I'm trying to run this proc by...

execute gw 'grand mound', 'kbsx', '7/4/2008', '7/25/2008'


but its giving the error

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@hotel".


I thought I was declaring that variable in the first parameter where i typed

'grand mound'

Does anyone know what I'm doing wrong?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-09 : 17:43:31
Here it is, again, because you deleted your other post
execute crosstab	'select da_date from greatwolfcleanedup where location like ' + quotename('%' + @hotel + '%', '''') + ' and roomtype like ' + quotename('%' + @roomtype + '%', '''') + ' and da_date >= ''' + convert(varchar(25), @datebegin, 112) + ''' and da_date < ''' + convert(varchar(25), dateadd(day, 1, @dateend), 112) + ''' group by da_date order by da_date'
'min(da_rate)',
'executiontime',
'greatwolfcleanedup'



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

fuzzyip
Starting Member

35 Posts

Posted - 2008-08-09 : 17:54:01
quote:
Originally posted by Peso

Here it is, again, because you deleted your other post
execute crosstab	'select da_date from greatwolfcleanedup where location like ' + quotename('%' + @hotel + '%', '''') + ' and roomtype like ' + quotename('%' + @roomtype + '%', '''') + ' and da_date >= ''' + convert(varchar(25), @datebegin, 112) + ''' and da_date < ''' + convert(varchar(25), dateadd(day, 1, @dateend), 112) + ''' group by da_date order by da_date'
'min(da_rate)',
'executiontime',
'greatwolfcleanedup'



E 12°55'05.25"
N 56°04'39.16"





I ended up doing this
execute crosstab
'select da_date from greatwolfcleanedup where location like ''%''+@hotel+''%'' and roomtype like ''%''+@roomtype+''%'' and da_date >= @datebegin and da_date < dateadd (d, +1, @dateend) group by da_date order by da_date', 'min(da_rate)', 'executiontime', 'greatwolfcleanedup'


is there anything wrong with it?

I tried to do yours instead, but it came back with

Msg 102, Level 15, State 1, Procedure gw2, Line 9
Incorrect syntax near '+'.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-10 : 02:42:35
shouldnt this be like this:-
execute crosstab
'select da_date from greatwolfcleanedup where location like ''%'''+@hotel+'''%'' and roomtype like ''%'''+@roomtype+'''%'' and da_date >='' + @datebegin and da_date < ''+ dateadd (d, +1, @dateend) +'' group by da_date order by da_date', 'min(da_rate)', 'executiontime', 'greatwolfcleanedup'
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-10 : 04:18:59
Yes, there are a lot of problems with that approach.
Use QUOTENAME function to remedy all sorts of escape characters.
execute crosstab	'select da_date from greatwolfcleanedup where location like ' + quotename('%' + @hotel + '%', '''') + ' and roomtype like ' + quotename('%' + @roomtype + '%', '''') + ' and da_date >= ''' + convert(varchar(25), @datebegin, 112) + ''' and da_date < ''' + convert(varchar(25), dateadd(day, 1, @dateend), 112) + ''' group by da_date order by da_date'
'min(da_rate)',
'executiontime',
'greatwolfcleanedup'



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

fuzzyip
Starting Member

35 Posts

Posted - 2008-08-10 : 13:42:11
I can't sucessfully create either procedures with those two queries, for both of them I get

Incorrect syntax near '+'.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-10 : 13:48:18
quote:
Originally posted by fuzzyip

I can't sucessfully create either procedures with those two queries, for both of them I get

Incorrect syntax near '+'.


but you told earlier you created it successfully and error is only in executing??
Go to Top of Page

fuzzyip
Starting Member

35 Posts

Posted - 2008-08-10 : 14:00:31
quote:
Originally posted by visakh16

quote:
Originally posted by fuzzyip

I can't sucessfully create either procedures with those two queries, for both of them I get

Incorrect syntax near '+'.


but you told earlier you created it successfully and error is only in executing??




Yes, I used mine to create it

create procedure gw
@hotel varchar(200),
@roomtype varchar(30),
@datebegin smalldatetime,
@dateend smalldatetime
as begin


execute crosstab
'select da_date from greatwolfcleanedup where location like ''%''+@hotel+''%'' and roomtype like ''%''+@roomtype+''%'' and da_date >= @datebegin and da_date < dateadd (d, +1, @dateend) group by da_date order by da_date', 'min(da_rate)', 'executiontime', 'greatwolfcleanedup'


end


The above executes, but I get this...

execute gw 'grand mound', 'kbsx', '7/4/2008', '7/25/2008'

output:
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@hotel".


You and Peso gave me different code to create the proc so I figured my coding was the reason for the error, but when I try to run both of yours code it gives me that + syntax error.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-10 : 14:08:34
aha...so you're trying to call crosstab procedure from another procedure. The error is because the variable @hotel is not inside cross tab stored procedure. you probably need to do like this

create procedure gw
@hotel varchar(200),
@roomtype varchar(30),
@datebegin smalldatetime,
@dateend smalldatetime
as begin

declare @sql varchar(8000)
set @sql='select da_date from greatwolfcleanedup where location like ''%''+@hotel+''%'' and roomtype like ''%''+@roomtype+''%'' +' and da_date >= '+ @datebegin +' and da_date < '+ dateadd (d, +1, @dateend) +' group by da_date order by da_date '

execute crosstab @sql, 'min(da_rate)', 'executiontime', 'greatwolfcleanedup'


end
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-10 : 14:16:44
Am I talking to blind people?
Use QUOTENAME.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

fuzzyip
Starting Member

35 Posts

Posted - 2008-08-10 : 14:25:05
quote:
Originally posted by visakh16

aha...so you're trying to call crosstab procedure from another procedure. The error is because the variable @hotel is not inside cross tab stored procedure. you probably need to do like this

create procedure gw
@hotel varchar(200),
@roomtype varchar(30),
@datebegin smalldatetime,
@dateend smalldatetime
as begin

declare @sql varchar(8000)
set @sql='select da_date from greatwolfcleanedup where location like ''%''+@hotel+''%'' and roomtype like ''%''+@roomtype+''%'' +' and da_date >= '+ @datebegin +' and da_date < '+ dateadd (d, +1, @dateend) +' group by da_date order by da_date '

execute crosstab @sql, 'min(da_rate)', 'executiontime', 'greatwolfcleanedup'


end




Using your code I get

Msg 156, Level 15, State 1, Procedure gw3, Line 9
Incorrect syntax near the keyword 'and'.
Msg 105, Level 15, State 1, Procedure gw3, Line 11
Unclosed quotation mark after the character string '


end
'.




I also tried changing JUST the select statement to what I was doing earlier that was successfully running, which actually did successfully complete, but upon execution, it still came back with Undeclared variable @hotel.

ahhh
Go to Top of Page
  Previous Page&nsp;  Next Page

- Advertisement -