| Author |
 Topic  |
|
fuzzyip
Starting Member
35 Posts |
 Posted - 08/09/2008 : 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 |
Edited by - fuzzyip on 08/09/2008 02:40:56
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 08/09/2008 : 02:48:18
|
| Did you get the error while creating procedure or while executing?If latter, what were values passed for params? |
 |
|
|
fuzzyip
Starting Member
35 Posts |
Posted - 08/09/2008 : 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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 08/09/2008 : 02:58:39
|
| one reason can be because pivot is a keyword in sql 2005. try wrapping it within [].use [pivot] instead of pivot |
|
|
|
fuzzyip
Starting Member
35 Posts |
Posted - 08/09/2008 : 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 |
Edited by - fuzzyip on 08/09/2008 14:13:51 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 08/09/2008 : 14:25:41
|
change this line too and try
EXEC ('SELECT ' + @pivot + ' AS [pivot] INTO ##pivot FROM ' + @table + ' WHERE 1=2') |
|
|
|
fuzzyip
Starting Member
35 Posts |
Posted - 08/09/2008 : 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?
|
Edited by - fuzzyip on 08/09/2008 15:38:39 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 08/09/2008 : 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" |
Edited by - SwePeso on 08/09/2008 15:39:13 |
|
|
|
fuzzyip
Starting Member
35 Posts |
Posted - 08/09/2008 : 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? |
Edited by - fuzzyip on 08/09/2008 15:45:18 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 08/09/2008 : 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" |
|
|
|
fuzzyip
Starting Member
35 Posts |
Posted - 08/09/2008 : 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? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 08/09/2008 : 17:43:31
|
Here it is, again, because you deleted your other postexecute 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" |
|
|
|
fuzzyip
Starting Member
35 Posts |
Posted - 08/09/2008 : 17:54:01
|
quote:
Originally posted by Peso
Here it is, again, because you deleted your other postexecute 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 '+'.
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 08/10/2008 : 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' |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 08/10/2008 : 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" |
|
|
|
fuzzyip
Starting Member
35 Posts |
Posted - 08/10/2008 : 13:42:11
|
I can't sucessfully create either procedures with those two queries, for both of them I get
Incorrect syntax near '+'. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 08/10/2008 : 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?? |
|
|
|
fuzzyip
Starting Member
35 Posts |
Posted - 08/10/2008 : 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. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 08/10/2008 : 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 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 08/10/2008 : 14:16:44
|
Am I talking to blind people?
Use QUOTENAME.
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
fuzzyip
Starting Member
35 Posts |
Posted - 08/10/2008 : 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 |
|
|
|
Topic |
|
|
|
The crosstab proc from SQLTeam Article not working
